我正在嘗試使用 2 個 count() 函式獲取公司收到和發出的唯一數量的發票。在 invoices 表中有兩列參考相同的公司 ID（一列是發送發票的公司的 ID，另一列是接收發票的公司的 ID）這是我嘗試使用的代碼：
SELECT K.ID,K.NAME,K.CITY, COUNT(*) AS NUM_OF_INVOICES_SENT, COUNT(*) AS NUM_OF_INVOICES_RECEIVED
FROM COMPANY K LEFT JOIN INVOICE F ON F.COMP_SNEDING = K.ID
GROUP BY K.NAME,K.ID,K.CITY
這是一個學校專案，所以我并不精通 sql/oracle
實際資料發票：
實際資料公司：
給定實際資料的預期結果：
uj5u.com熱心網友回復：
這是一種選擇；它不使用count，但sum有case表達。
樣本資料：
SQL> with
2 invoice (id, amount, comp_sending, comp_receiving) as
3 (select 1, 2000 , 1, 2 from dual union all
4 select 2, 28250, 3, 2 from dual union all
5 select 3, 8700 , 4, 1 from dual union all
6 select 4, 20200, 5, 3 from dual union all
7 select 5, 21500, 3, 4 from dual
8 ),
9 company (id, name, city, state) as
10 (select 1, 'Microsoft', 'Redmond' , 'Washington' from dual union all
11 select 2, 'Ubisoft' , 'Paris' , 'France' from dual union all
12 select 4, 'Starbucks', 'Seattle' , 'Washington' from dual union all
13 select 5, 'Apple' , 'Cupertino', 'California' from dual union all
14 select 3, 'Nvidia' , 'Cupertino', 'California' from dual
15 )
查詢從這里開始：
16 select c.id, c.name,
17 sum(case when c.id = i.comp_sending then 1 else 0 end) cnt_sent,
18 sum(case when c.id = i.comp_receiving then 1 else 0 end) cnt_received
19 from company c left join invoice i on c.id in (i.comp_sending, i.comp_receiving)
20 group by c.id, c.name
21 order by c.id;
ID NAME CNT_SENT CNT_RECEIVED
---------- --------- ---------- ------------
1 Microsoft 1 1
2 Ubisoft 0 2
3 Nvidia 2 1
4 Starbucks 1 1
5 Apple 1 0
SQL>
uj5u.com熱心網友回復：
如果將 CASE 運算式中的 0 替換為 NULL，則可以使用 COUNT。所以@Littlefoot 的查詢變成了
select c.id, c.name,
COUNT(case when c.id = i.comp_sending then 1 else NULL end) cnt_sent,
COUNT(case when c.id = i.comp_receiving then 1 else NULL end) cnt_received
from company c left join invoice i on c.id in (i.comp_sending, i.comp_receiving)
group by c.id, c.name
order by c.id;
這是有效的，因為COUNT僅計算正在計算的運算式中具有非 NULL 值的那些行。
db<>在這里擺弄
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/486119.html
標籤：甲骨文