mysql按天分组

Question

我有这个方案:

+-------+
|posts: |
+----+--+--------+--------------------+
| ID |   title   |      timestamp     |
+----+-----------+--------------------+
|  1 |    t1     |2011-04-05 17:54:55 |
+----+-----------+--------------------+ 
|  2 |    t2     |2011-04-06 09:10:11 |
+----+-----------+--------------------+ 
|  3 |    t3     |2011-04-07 02:07:22 |
+----+-----------+--------------------+ 

我如何获得过去 7 天的帖子总数，如下所示:

monday: 3
Tuesday: 9
Wednesday: 2

Answer 1

MySQL 特定解决方案:

SELECT WEEKDAY(timestamp_field) AS wd, count(*) FROM your_table GROUP BY wd;

或

SELECT count(*) FROM your_table GROUP BY WEEKDAY(timestamp_field);