我有一个企业及其创建日期的列表,我想将它们分组为年份+周数的箱-但是要注意的是,如果2015年第1周没有创建任何企业,我仍然希望它显示此箱'0'。
以下查询跳过了没有创建任何业务的几周:
SELECT Date_Format(created,'%X %V') as d, count(*) as Biz
FROM businesses b
GROUP BY d
ORDER BY d asc
有人知道如何添加丢失的垃圾箱并保持动态吗?这意味着当我在2周内运行它时,即使在这2周内没有创建任何公司,我也会获得2个新垃圾箱。
谢谢!
最佳答案
尝试附加的查询
select
Date_Format(M1,'%X %V'),count(b.*)
from
(
select
'2015-01-01' +INTERVAL m WEEK as m1
from
(
select @rownum:=@rownum+1 as m from
(select 1 union select 2 union select 3 union select 4) t1,
(select 1 union select 2 union select 3 union select 4) t2,
(select 1 union select 2 union select 3 union select 4) t3,
(select @rownum:=-1) t0
) d1
) d2
LEFT JOIN businesses b on Date_Format(b.created,'%X %V')=Date_Format(M1,'%X %V')
group by
Date_Format(M1,'%X %V')
order by m1