过去7天的MySQL计数记录按0记录分组,
过去两天我一直在试图弄清楚这一点。这是我认为很容易的事情,但是我不能在我的生活中找出所需的SQL查询。我找到了一些相关的问题/答案,但不完全是我遇到的问题。过去7天的MySQL计数记录按0记录分组,
我试图获得本周过去7天的记录计数,并按分支位置对其进行分组,并且在没有找到记录时也包括0。每个人都说的一件事是,我需要生成一个日历/日期助手表,然后加入它。我已经完成了这项工作,现在我有一个日历表,日期在2000-01-01
和2040-01-01
之间。
这里是我的表结构如下所示:
Records | location | date | thing | |----------|------------|---------| | Branch 1 | 2017-04-01 | Thing 1 | | Branch 2 | 2017-04-03 | Thing 2 | | Branch 1 | 2017-04-03 | Thing 3 | | Branch 1 | 2017-04-01 | Thing 4 | | Branch 3 | 2017-04-01 | Thing 5 | | Branch 3 | 2017-04-02 | Thing 6 | | Branch 1 | 2017-04-02 | Thing 7 | | Branch 2 | 2017-04-07 | Thing 8 |
让我们假设它目前2017-04-07
。请注意,并非所有包含2017-04-01
和2017-04-07
的日期都在记录表中,这就是我需要日历助手表的原因。话虽这么说,我试图让下面的输出:
Output | location | date | count(things)| |----------|------------|--------------| | Branch 1 | 2017-04-01 | 2 | | Branch 1 | 2017-04-02 | 1 | | Branch 1 | 2017-04-03 | 1 | | Branch 1 | 2017-04-04 | 0 | | Branch 1 | 2017-04-05 | 0 | | Branch 1 | 2017-04-06 | 0 | | Branch 1 | 2017-04-07 | 0 | | Branch 2 | 2017-04-01 | 0 | | Branch 2 | 2017-04-02 | 0 | | Branch 2 | 2017-04-03 | 1 | | Branch 2 | 2017-04-04 | 0 | | Branch 2 | 2017-04-05 | 0 | | Branch 2 | 2017-04-06 | 0 | | Branch 2 | 2017-04-07 | 1 | | Branch 3 | 2017-04-01 | 1 | | Branch 3 | 2017-04-02 | 1 | | Branch 3 | 2017-04-03 | 0 | | Branch 3 | 2017-04-04 | 0 | | Branch 3 | 2017-04-05 | 0 | | Branch 3 | 2017-04-06 | 0 | | Branch 3 | 2017-04-07 | 0 |
所以,即使有零条记录,我还是要显示该位置的线路和日期(过去7天) 。这是否可以实现?
这里是我一直插科打诨与查询:
SELECT
`records`.`location`,
`calendar`.`date`,
COUNT(`records`.`thing`) AS `count`
FROM `records`
RIGHT JOIN `calendar` ON `records`.`date` = `calendar`.`date`
WHERE `calendar`.`date` >= '2017-04-01' AND `calendar`.`date` <= '2017-04-07'
GROUP BY `calendar`.`date`, `records`.`location`
ORDER BY `records`.`location` ASC, `calendar`.`date` ASC
和
SELECT
`records`.`location`,
`date`.`ymd`,
COUNT(`records`.`thing`) AS `count`
FROM (
SELECT
`calendar`.`date` AS `ymd`
FROM `calendar`
WHERE `calendar`.`date` >= '2017-04-01' AND `calendar`.`date` <= '2017-04-07'
) `date`
LEFT JOIN `records` ON `date`.`ymd` = `records`.`date`
GROUP BY `records`.`location`, `date`.`ymd`
两个查询给我这甚至还没有接近我正在寻找相同的结果对于。
请帮忙!
这不仅是日期,你需要一个完整的清单,但分支机构也是如此。我添加了一个包含所有位置的派生表,并将其交叉添加到以前的结果集中。另外,选择列表中的位置字段和group by子句必须来自此派生表。
SELECT
t.`location`,
`calendar`.`date`,
COUNT(`records`.`thing`) AS `count`
FROM `records`
RIGHT JOIN (`calendar`
JOIN (select distinct location from records) t) ON `records`.`date` = `calendar`.`date` and t.location=records.location
WHERE `calendar`.`date` >= '2017-04-01' AND `calendar`.`date` <= '2017-04-07'
GROUP BY `calendar`.`date`, t.`location`
ORDER BY `records`.`location` ASC, `calendar`.`date` ASC
完美!谢谢! –
你有分支机构/位置表吗? – Sal