cross join 日历到不同的项目和 left join items表以获取缺少的行。

select i.itemid,c.month_last_day
from calendar c 
cross join (select distinct itemid from items) i
left join items it on it.itemid = i.itemid and c.month_last_day = it.dt
where it.dt is null

在配置单元中使用交叉联接和左外部联接进行查询。

with calendar as 
(select '20190131' last_day union all
 select '20190229' last_day union all
 select '20190331' last_day union all
 select '20190430' 
) 
,items as 
(select 101 itemid,'20190131' dt union all
 select 101 itemid,'20190229' dt union all
 select 101 itemid,'20190331' dt union all
 select 102 itemid,'20190131' dt union all
 select 102 itemid,'20190331' dt union all
 select 102 itemid,'20190430' dt
),
res1 as 
(select i.itemid, c.last_day from calendar c, (select distinct itemid from items) i)

select res1.itemid, res1.last_day from res1 left outer join items i on res1.itemid = i.itemid and res1.last_day=i.dt where i.dt is null;