MySQL GROUP BY 按周、月查询分组结果

Question

我有一个名为“counter”的表，它有 3 个字段:

| Field  | Type       | Null | Key | Default | Extra          |
+--------+------------+------+-----+---------+----------------+
| id     | int(11)    | NO   | PRI | NULL    | auto_increment |
| record | datetime   | YES  |     | NULL    |                |
| passed | tinyint(1) | YES  |     | NULL    |                |
+--------+------------+------+-----+---------+----------------+

我的表记录

mysql> SELECT record, passed FROM counter;
+---------------------+--------+
| record              | passed |
+---------------------+--------+
| 2019-09-19 00:00:00 |      1 |

我想进行查询，以根据现场记录按周和月对结果进行分组。

我尝试过这样做，但看起来不对

 SELECT DATE_FORMAT(record, '%d-%m-%Y') AS ts, COUNT(*) FROM counter WHERE record >= '2019-10-09' AND   record <  '2019-10-09' + INTERVAL 7 DAY GROUP BY DATE_FORMAT(record, '%d-%m-%Y');

Answer 1

带有日期提取的简单分组必须有效

select 
    EXTRACT(YEAR FROM record) V_YEAR,
    EXTRACT(MONTH FROM record) V_MONTH,
    EXTRACT(WEEK FROM record) V_WEEK,
    COUNT(*)
FROM COUNTER
GROUP BY
    EXTRACT(YEAR FROM record) ,
    EXTRACT(MONTH FROM record) ,
    EXTRACT(WEEK FROM record) 

使用 DATE_FORMAT(date, format) 编辑相同的作品

select 
    DATE_FORMAT(record, '%Y') V_YEAR,
    DATE_FORMAT(record, '%M') V_MONTH,
    DATE_FORMAT(record, '%d') V_WEEK,
    COUNT(*)
FROM COUNTER
GROUP BY
    DATE_FORMAT(record, '%Y') ,
    DATE_FORMAT(record, '%M') ,
    DATE_FORMAT(record, '%d')