我正试图从一个查询中生成一个结果,该查询列出了从今天(2020/07/15(起的最后7天以及与特定代码匹配的视图。如果当天代码没有视图,我希望当天返回0。
表格格式
DAY | CODE | VIEWS
2020-07-10 | 123 | 5
2020-07-11 | 123 | 2
2020-07-12 | 123 | 3
2020-07-15 | 123 | 8
2020-07-15 | 124 | 2
2020-07-15 | 125 | 2
代码123的预期结果
DAY | VIEWS
2020-07-09 | 0
2020-07-10 | 5
2020-07-11 | 2
2020-07-12 | 3
2020-07-13 | 0
2020-07-14 | 0
2020-07-15 | 8
我已经找到了一种从这里生成日历日期并根据我的需求进行调整的方法,但我不知道如何将结果与我的表联系起来。
select * from
(select
adddate(NOW() - INTERVAL 7 DAY, t0) day
from
(select 1 t0
union select 1
union select 2
union select 3
union select 4
union select 5
union select 6
union select 7) t0) v
需要任何帮助。
一个选项使用递归查询-在MySQL 8.0中可用:
with recursive cte as (
select current_date - interval 6 day dt
union all
select dt + interval 1 day from cte where dt < current_date
)
select c.dt, coalesce(sum(t.views), 0) views
from cte
left join mytable t on t.day = c.dt
group by c.dt
order by c.dt
您也可以手动构建派生表,正如您最初想要的那样(这将适用于所有版本的MySQL(:
select current_date - interval d.n day dt, coalesce(sum(t.views), 0) views
from (
select 0 n
union all select 1
union all select 2
union all select 3
union all select 4
union all select 5
union all select 6
) d
left join mytable t on t.day = current_date - interval d.n day
group by d.n
order by d.n desc