sql - MYSQL 查询 : count the dates

Question

在下表中

+-------+-----------------------+ 
| id    | timestamp             | 
+-------+-----------------------+ 
| 1     | 2010-06-10 14:35:30   | 
| 2     | 2010-06-10 15:27:35   | 
| 3     | 2010-06-10 16:39:36   | 
| 4     | 2010-06-11 14:55:30   | 
| 5     | 2010-06-11 18:45:31   | 
| 6     | 2010-06-12 20:25:31   | 
+-------+-----------------------+ 

我希望能够计算日期(忽略时间)。所以输出应该如下所示:

+-------+-----------------------+ 
| id    | type         | count  |
+-------+-----------------------+ 
| 1     | 2010-06-10   | 3      |
| 2     | 2010-06-11   | 2      |
| 3     | 2010-06-12   | 1      |
+-------+-----------------------+

对此的查询是什么？

Answer 1

如果您可以在结果中没有 id 列的情况下工作:

SELECT DATE(timestamp) AS type, COUNT(*) AS `count`
FROM sometable
GROUP BY DATE(timestamp)
ORDER BY DATE(timestamp)