当cte不匹配时包含cte的值

debugcn 发表于 Dev

贾努斯·恩斯特罗姆（JanusEngstrøm）

在我的表格中，我有一些条目-通过表格的日期列-不早于2016年1月4日（2016年1月4日）。现在，我想进行一个查询，该查询或多或少地对具有特定日期值的行数进行计数，但是我希望此查询能够为表中不存在的日期返回0计数。

我有这个：

with date_count as (select '2016-01-01'::date + CAST(offs || ' days' as
interval) as date from generate_series(0, 6, 1) AS offs ) select
date_count.date, count(allocation_id) as packs_used from medicine_allocation,
date_count where site_id = 1 and allocation_id is not null and timestamp
between date_count.date and date_count.date + interval '1 days' group by
date_count.date order by date_count.date;

这无疑为我提供了表中日期的一个很好的汇总视图，但是由于没有行来自2016年1月4日之前，因此它们不会显示在结果中：

"2016-01-04 00:00:00";1
"2016-01-05 00:00:00";2
"2016-01-06 00:00:00";4
"2016-01-07 00:00:00";3

我想这样：

"2016-01-01 00:00:00";0
"2016-01-02 00:00:00";0
"2016-01-03 00:00:00";0
"2016-01-04 00:00:00";1
"2016-01-05 00:00:00";2
"2016-01-06 00:00:00";4
"2016-01-07 00:00:00";3

我也尝试过在CTE上进行正确的联接，但这会产生相同的结果。我不太了解如何执行此操作……那里有什么帮助吗？

最好的，贾纳斯

戈登·利诺夫（Gordon Linoff）

您只需要一个left join：

with date_count as (
      select '2016-01-01'::date + CAST(offs || ' days' as
interval) as date
      from generate_series(0, 6, 1) AS offs
     )
select dc.date, count(ma.allocation_id) as packs_used
from date_count dc left join
     medicine_allocation ma
     on ma.site_id = 1 and ma.allocation_id is not null and
        ma.timestamp between dc.date and dc.date + interval '1 days'
group by dc.date
order by dc.date;

忠告：不要在FROM子句中使用逗号。始终使用显式JOIN语法。

您还将注意到，where条件已移至该ON子句。这是必需的，因为它们在第二个表上。

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。