MySQL WEEK():获取日期范围内的所有星期(有/无记录) [英] MySQL WEEK() : Get all weeks in date range (with/without records)

问题描述

这是一个SQL查询，用于获取按周分隔的表中的记录数(仅将日期存储在表中).它按预期工作.

Here's an sql query I am using to get count of records in a table separated by week (only date is stored in table). It works as expected.

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查询本身来实现.是否有可能?谢谢.

That is, if no records found for a particular week it should still show the week (within the date range of records in table) with a count of 0. I can figure out a way to do this in PHP, but I was wondering if it can be achieved with a little tweaking of the SQL query itself. Is it possible? Thanks.

SQLFiddle

推荐答案

假设您有一个整数表(以下称为`numbers`):

Assuming you have a table of integers (called `numbers` below):

   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提琴

这篇关于MySQL WEEK():获取日期范围内的所有星期(有/无记录)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！