mysql - 如何在没有硬编码的情况下获取时间范围数据

Question

我喜欢以下查询。现在我想改变3 days ago from now()

我总是硬编码如下

select 
id
,coalesce(sum(case when snapshot_day in ('2021-05-03') then 1 else 0 end), 0) AS "2021-05-03"
,coalesce(sum(case when snapshot_day in ('2021-05-04') then 1 else 0 end), 0) AS "2021-05-04"
,coalesce(sum(case when snapshot_day in ('2021-05-05') then 1 else 0 end), 0) AS "2021-05-05"
from talble
where type in ('Daily')
group by 1

有没有什么好方法可以在没有硬编码的情况下获得结果？

如果有一些例子，有人有意见，请告诉我

谢谢

标签： mysqlsql

Answer 1

你可以使用 now() ：

select 
id
,coalesce(sum(case when snapshot_day = cast(now() as date) - interval 2 day  then 1 else 0 end), 0) AS "Day before yesterday"
,coalesce(sum(case when snapshot_day = cast(now() as date) - interval 1 day  then 1 else 0 end), 0) AS yesterday
,coalesce(sum(case when snapshot_day = cast(now() as date) then 1 else 0 end), 0) AS today
from talble
where type in ('Daily')
and snapshot_day >= cast(now() as date) - interval 2 day
group by id