Mysql查询只给出一条记录,但count函数给出的计数多于一条 .
[编辑]我的基本目标是计算尝试调查的用户的数量 . 在表格中,每一行代表调查中每个问题的答案 . 所以每行都包含用户ID .
Database table Schema(table):
+-------------+------------------+------+-----+---------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+------------------+------+-----+---------------------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| survey_id | int(10) unsigned | NO | | NULL | |
| user_id | int(10) unsigned | NO | | NULL | |
+-------------+------------------+------+-----+---------------------+----------------+
Data in Table(table):
+----+-----------+---------+
| id | survey_id | user_id |
+----+-----------+---------+
| 1 | 1 | 1 |
| 2 | 1 | 1 |
| 3 | 1 | 1 |
+----+-----------+---------+
3 rows in set (0.00 sec)
COUNT个查询:
SELECT count(*) AS total FROM `table` WHERE survey_id = 1 GROUP BY user_id
结果:
+-------+
| total |
+-------+
| 3 |
+-------+
1 row in set (0.00 sec)
简单查询:
SELECT id,user_id,survey_id AS total FROM `table` WHERE survey_id = 1 GROUP BY user_id
结果:
+----+---------+-------+
| id | user_id | total |
+----+---------+-------+
| 1 | 1 | 1 |
+----+---------+-------+
1 row in set (0.00 sec)
Desire Result:
行数和行数应相同 .
那么,为什么第二个查询返回单行和第一个计数查询返回不是一个 .
如果还不清楚,请告诉我 .
任何帮助都会提前明确 .
1 回答
如果要计算
GROUP BY
返回的组数,可以使用COUNT(distinct user_id)