是否可以透過 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;