mysql查询获取一年中没有数据的几周的行

Question

我必须显示连续几周没有结果的行(基于我的数据库中的时间戳列)。我创建了一个临时“周”表，并根据其他 SO Q/A 形成了以下查询:

CREATE TEMPORARY TABLE weeks (weekNo INT);

INSERT INTO weeks (weekNo) 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 
   WeekNo, 
   SUBDATE(DATE(i.TimeStamp), 
   INTERVAL WEEKDAY(DATE(i.TimeStamp)) DAY) AS date_of_week, 
   COUNT(distinct PatientID) PatientCount,
   COUNT(distinct PatientID, AcquisitionDate) StudyCount
FROM image_info i 
RIGHT OUTER JOIN weeks w ON WEEK(DATE(i.TimeStamp)) = WeekNo 
GROUP BY WeekNo;

这让我来到这里:

WeekNo    date_of_week    PatientCount    StudyCount
------    ------------    ------------    ----------
5         2017-01-30      19              19
6         2017-02-06      28              28
9         2017-02-27      15              15

但我想看到这个:

WeekNo    date_of_week    PatientCount    StudyCount
------    ------------    ------------    ----------
1         2017-01-01      0               0
2         2017-01-08      0               0
3         2017-01-15      0               0
4         2017-01-22      0               0
5         2017-01-30      19              19
6         2017-02-06      28              28
7         2017-02-12      0               0
8         2017-02-19      0               0
9         2017-02-27      15              15

关于我在查询中做错了什么有什么想法吗？

Answer 1

尝试反转查询中的表。

使用第一周表并将其与 image_info 表进行左外连接。