sql - 按类别加入

Question

有没有办法根据 sql.xml 中的类别填充缺失的记录。例如 dat2 表是标准表。但是类别 ABC 没有 3 月 9 日，同样 BCG 也没有 3 月 23 日。

with dat1 as(
select convert(date, DATEADD(day,+14,getdate()), 103)  as [date1], 'ABC' as Fund, 2 as val union 
select convert(date, getdate(), 103)  as [date1], 'BCG' as Fund, 2 as val ),

dat2 as 
(select convert(date, DATEADD(day,+14,getdate()), 103)  as [date] union select convert(date, getdate(), 103)  as [date] )

--select * from dat1
select *  from dat1
full outer join dat2 on [date1] = [date]

下面的输出

date1         Fund  val date
2021-03-09     BCG  2   2021-03-09
2021-03-23     ABC  2   2021-03-23

预期产出

date1         Fund  val date
2021-03-09     BCG  2   2021-03-09
2021-03-23     ABC  2   2021-03-23
2021-03-09     ABC  0   2021-03-09
2021-03-23     BCG  0   2021-03-23

有可能实现吗？

标签： sqltsql

Answer 1

不确定这是否是最佳选择，但它会成功。

with dat1 as(
select convert(date, DATEADD(day,+14,getdate()), 103)  as [date1], 'ABC' as Fund, 2 as val  union 
select convert(date, getdate(), 103)  as [date1], 'BCG' as Fund, 2 as val ),

dat2 as 
(select convert(date, DATEADD(day,+14,getdate()), 103)  as [date] union select convert(date, getdate(), 103)  as [date] )

select t.date1,t.fund,coalesce(d.val,0)val,t.date from (select dat1.date1,f.fund,dat1.val ,dat2.date from dat1 
full outer join dat2 on [date1] = [date]
,(select fund from dat1)F)t
left join dat1 d on t.fund=d.fund and t.date1=d.date1
order by val desc ,date1
GO

日期1	基金	值	日期
2021-03-09	卡介苗	2	2021-03-09
2021-03-23	美国广播公司	2	2021-03-23
2021-03-09	美国广播公司	0	2021-03-09
2021-03-23	卡介苗	0	2021-03-23

db<>在这里摆弄