在一个查询中分别为两个不同日期生成两个SUM值列

Question

我正在尝试组合两个查询

select s.id , sum(h.val)  as [VAL]
from identity s inner join price_net h on s.date = h.date and s.num_id = h.num_id and s.rec_type = h.rec_type
where s.date = '10/25/21'
and s.rec_type = 'D'
and s.tier = 'P'
group by s.

select s.id , sum(h.val)  as [VAL]
from identity s inner join price_net h on s.date = h.date and s.num_id = h.num_id and s.rec_type = h.rec_type
where s.date = '10/26/21'
and s.rec_type = 'D'
and s.tier = 'P'
group by s.

我想要它，这样就有一个表，其中一列是s.id，下一列是每个日期的VAL列。如果给定的s.id在特定日期没有可用的值，则应显示NA.

Answer 1

我支持您正在查找条件聚合(聚合函数中的CASE WHEN(。

select
i.id,
sum(case when i.date = date '2021-10-25' then pn.val end) as val_20211025,
sum(case when i.date = date '2021-10-26' then pn.val end) as val_20211026
from identity i
join price_net pn on pn.date = i.date 
and pn.num_id = i.num_id
and pn.rec_type = i.rec_type
where i.tier = 'P'
and i.rec_type = 'D'
and i.date in (date '2021-10-25', date '2021-10-26')
group by i.id
order by i.id;

Answer 2

当两个条件都是时，您可以使用case

select s.id , sum(CASE WHEN s.date = '10/25/21' THEN h.val eLSE  O END)  as [VAL]
, sum(CASE WHEN s.date = '10/26/21' THEN h.val eLSE  O END)  as [VAL2]
from identity s inner join price_net h on s.date = h.date and s.num_id = h.num_id and s.rec_type = h.rec_type
where 
as.rec_type = 'D'
and s.tier = 'P'
group by s.ìd