是否可以通过 join 按列分组？

Question

select tc.UIDPK,
count(torder.status) as total
from TCUSTOMER tc
inner join TORDER torder on tc.UIDPK=torder.CUSTOMER_UID
where tc.UIDPK=490000;

上面的查询对我来说效果很好。 但状态可以是 IN_PROGRESS、FAILED、ON_HOLD

我如何编写返回状态计数的查询 喜欢 tc.UIDPK、总订单数、IN_PROGRESS 订单数、总订单数-IN_PROGRESS 订单数。 我尝试了下面但没有工作

select tc.UIDPK,
count(torder.status) as total,
count(torder2.status) as inprogress,
count(torder.status)-count(torder2.status) as remaining
from TCUSTOMER tc
inner join TORDER torder on tc.UIDPK=torder.CUSTOMER_UID
left join TORDER torder2 on tc.UIDPK=torder2.CUSTOMER_UID and torder2.status in('IN_PROGRESS')
where tc.UIDPK=490000;

Answer 1

不需要多次连接，使用SUM。

尝试

select tc.UIDPK,
       COUNT(torder.status) as total,
       SUM(torder.status = 'IN_PROGRESS') as inprogress,
       COUNT(torder.status) - SUM(torder.status = 'IN_PROGRESS') as remaining
from TCUSTOMER tc
inner join TORDER torder on tc.UIDPK=torder.CUSTOMER_UID
where tc.UIDPK=490000
group by tc.UIDPK;