如何在SQL中将所有行放在同一级别

Question

尝试将此查询用于客户请求贷款的过程：

select loan_id,
CASE WHEN status = 'document_sent' then date ELSE NULL END as document_sent,
CASE WHEN status = 'document_rejected' then date ELSE NULL END as document_rejected
from table
and status in ('document_sent', 'document_rejected')
order by 2 asc, 3 asc

结果是：

loan_id
123		123		123

如果我们不对每种类型的记录的缺失/存在做出任何假设：

select 
coalesce(a.loan_id,b.loan_id) as loan_id
, ds.date as document_sent
, dr.date as document_rejected
from 
(select * from tbl where status='document_sent') ds
full outer join
(select * from tbl where status='document_rejected') dr
on ds.loan_id=dr.loan_id

如果你知道一个或另一个状态总是存在，那么它可以稍微简化(可以变成left join(，或者如果两个记录都在那里，它可以简化为：

select 
a.loan_id
, ds.date as document_sent
, dr.date as document_rejected
from 
tbl ds
inner join
tbl dr
on ds.loan_id=dr.loan_id
and ds.status='document_sent'
and dr.status='document_rejected'

如果`完全外部联接不可用：

select 
coalesce(a.loan_id,b.loan_id) as loan_id
, ds.date as document_sent
, dr.date as document_rejected
from 
(select * from tbl where status='document_sent') ds
left join
(select * from tbl where status='document_rejected') dr
on ds.loan_id=dr.loan_id
union all
select 
coalesce(a.loan_id,b.loan_id) as loan_id
, ds.date as document_sent
, dr.date as document_rejected
from 
(select * from tbl where status='document_sent') ds
right join
(select * from tbl where status='document_rejected') dr
on ds.loan_id=dr.loan_id
where ds.loan_id is null

感谢@nbk的指出。

你也可以使用最小/最大(虽然我们不想找到任何东西的最小/最大，但我们想利用他们的行为，1.他们接受日期，2。它们忽略空值，3。它们与GROUP BY一起用于将行分组(：

select 
loan_id
, min(case when status='document_sent' 
then ds.date else NULL end) as document_sent
, min(case when status='document_rejected' 
then ds.date else NULL end) as document_rejected
from 
tbl
group by id