MySQL的WEE​​K（）：获取日期范围内的所有周（有/无记录）(MySQL WEEK() : G

下面是我使用的间隔一周（仅日期存储在表）表得到的记录数的SQL查询。 它按预期工作。

SELECT count(id), CONCAT('Week ',WEEK(complaintRaisedDate)) week
FROM events
WHERE categoryId=1
GROUP BY week
ORDER BY week

这产生类似结果

count(id)       week
---------- | ----------

1               Week 36
2               Week 40
1               Week 41

我多么希望得到的结果是低于：

count(id)       week
---------- | ----------
1               Week 36
0               Week 37
0               Week 38
0               Week 39
2               Week 40
1               Week 41

也就是说，如果发现某一周，应该仍然显示本周的0计数我能想出办法在PHP中做到这一点（在表中记录的日期范围内）没有记录，但我在想，如果它可以与SQL查询本身的小调整来实现。 可能吗？ 谢谢。

编辑： SQLFiddle

Answer 1:

假设你有一个整数的表（称为`numbers`下文）：

   SELECT COALESCE(n, 0) AS num_complaints, CONCAT('Week ', i) AS `week`
     FROM (SELECT i
             FROM numbers
            WHERE i BETWEEN (SELECT WEEK(MIN(complaintRaisedDate)) FROM events LIMIT 1)
                            AND
                            (SELECT WEEK(MAX(complaintRaisedDate)) FROM events LIMIT 1))
          week_ranges
LEFT JOIN (  SELECT count(id) AS n, WEEK(complaintRaisedDate) AS weeknum
               FROM events
              WHERE categoryId=1
           GROUP BY weeknum) weekly_tallies
       ON week_ranges.i = weekly_tallies.weeknum
 ORDER BY `week` ASC;

SQL小提琴

Answer 2:

尝试： http://sqlfiddle.com/#!2/5dfbf/36

CREATE  TABLE weeks (
         id INT
       );
INSERT INTO weeks (id) VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11), (12), (13), (14), (15), (16), (17), (18), (19), (20), (21), (22), (23), (24), (25), (26), (27), (28), (29), (30), (31), (32), (33), (34), (35), (36), (37), (38), (39), (40), (41), (42), (43), (44), (45), (46), (47), (48), (49), (50), (51), (52), (53), (54);



    SELECT count(events.id), ifnull(CONCAT('Week ',WEEK(complaintRaisedDate)),0) week
FROM events RIGHT OUTER JOIN weeks ON WEEK(events.complaintRaisedDate) = weeks.id
GROUP BY weeks.id 
HAVING weeks.id>=(SELECT MIN(WEEK(events.complaintRaisedDate)) FROM events)
AND weeks.id<=(SELECT MAX(WEEK(events.complaintRaisedDate)) FROM events);

文章来源: MySQL WEEK() : Get all weeks in date range (with/without records)