我正在尝试创建一个查询,该查询按日期和时间提供ids
细分,其中空白结果显示为零。
这是到目前为止的 MYSQL 查询:
SELECT DATE(received),
CONCAT(hourly.hour, ':00 - ', hourly.hour+1, ':00') AS Hours,
COALESCE(COUNT(id),0) AS "Leads"
FROM hourly
LEFT JOIN digital_lead ON hourly.hour=hour(digital_lead.received)
WHERE
digital_lead.received>=DATE_SUB('2014-11-01', INTERVAL 24 HOUR) AND
digital_lead.received<=DATE_SUB('2014-11-26', INTERVAL 24 HOUR)
GROUP BY DATE(received), hourly.hour
ORDER BY DATE(received)
我有一个从 0 到 23 的整数表,组成了hourly
表。
目前,此查询仅显示存在结果的时隙 - 如下所示:
DATE(received) Hours Leads
2014-11-12 11:00 - 12:00 23
2014-11-12 12:00 - 13:00 19
2014-11-12 13:00 - 14:00 18
2014-11-12 14:00 - 15:00 17
2014-11-12 15:00 - 16:00 23
2014-11-12 16:00 - 17:00 13
2014-11-12 17:00 - 18:00 17
查询应显示00:00 - 11:00
中时隙的零结果,而不是从查询结果中遗漏它们。它似乎忽略了COALESCE
函数,我尝试用没有区别的IFNULL(COUNT(id), 0)
替换它。
我已经尝试了这个问题/答案中的解决方案 - 堆栈溢出链接
但是,这对我不起作用。
有什么建议吗?
编辑
这是一个SQL FIDDLE以获取更多详细信息
这需要一些时间。这是我能想到的最好的办法。可能有更好的方法可以做到这一点,但加入时缺少日期或时间总是很复杂。我给出的答案很少与缺少日期有关,但这完全不同。
因此,使用相同的想法,我在查询中进行了一些更改以检索数据,如下所示
select
t1.date_received,
t1.Hours,
COALESCE(t2.`Leads`,0) as `Leads`
from
(
select
distinct DATE(received) as date_received,
concat (DATE(received),'-',h.hour) as date_hour,
CONCAT(h.hour, ':00 - ', h.hour+1, ':00') AS Hours
from digital_lead
cross join (select hour from hourly)h
)t1
left join
(
SELECT DATE(received) as date_received,
concat (DATE(received),'-',hour) as date_hour,
CONCAT(hourly.hour, ':00 - ', hourly.hour+1, ':00') AS Hours,
COALESCE(COUNT(id),0) AS "Leads"
FROM hourly
LEFT JOIN digital_lead ON hourly.hour=hour(digital_lead.received)
and digital_lead.received>=DATE_SUB('2014-11-01', INTERVAL 24 HOUR)
and digital_lead.received<=DATE_SUB('2014-11-26', INTERVAL 24 HOUR)
GROUP BY DATE(received), hourly.hour
)t2
on t1.date_hour = t2.date_hour
order by t1.date_received,
cast(substring_index(t1.Hours,':',1) as unsigned)
小提琴