CREATE TABLE tbl
(
id INT,
time DATE
);INSERT INTO tbl(id, time) VALUES(1, '2009-06-07'), (1, '2009-06-07'), (1, '2009-06-07'),
(2, '2009-06-07'), (3, '2009-06-07'), (3, '2009-06-07'),
(3, '2009-06-07'), (1, '2009-06-08'), (2, '2009-06-08'),
(2, '2009-06-08'), (4, '2009-06-08'), (5, '2009-06-08');如此建表并插入数据,我想统计每天出现的id号的数量。
如所插入的数据,06-07出现过的id为1,2,3 所以统计结果为3,
同理,8号出现过1,2,4,5 所以结果为4.这样的sql应该如何写?
+------+------------+
| id | time |
+------+------------+
| 1 | 2009-06-07 |
| 1 | 2009-06-07 |
| 1 | 2009-06-07 |
| 2 | 2009-06-07 |
| 3 | 2009-06-07 |
| 3 | 2009-06-07 |
| 3 | 2009-06-07 |
| 1 | 2009-06-08 |
| 2 | 2009-06-08 |
| 2 | 2009-06-08 |
| 4 | 2009-06-08 |
| 5 | 2009-06-08 |
+------+------------+
12 rows in set (0.09 sec)mysql> select count(distinct id) from tbl group by time;
+--------------------+
| count(distinct id) |
+--------------------+
| 3 |
| 4 |
+--------------------+
2 rows in set (0.08 sec)mysql>
SELECT TIME,ID FROM TT GROUP BY TIME,ID) A1 GROUP BY TIME