我有一張表 A：
entity_id name
------------------
1 Test1
2 Test2
3 Test3
4 Test4
5 Test5
6 Test6
我有一張表 B：
entity_id value1 value2
-----------------------------
1 10 20
1 15 30
2 10 25
1 9 45
3 null 1
2 45 50
3 20 null
我需要撰寫一個查詢來從表 A 中選擇 entity_id 和名稱，并計算表 B 中列 value1 和 value2 的 entity_id 的總出現次數，然后計算這些列數的總和（null 不計算在內）。
所以我的輸出表是：
entity_id name value1_count value2_count total_count
----------------------------------------------------------------------
1 Test1 3 3 6
2 Test2 1 2 3
3 Test3 1 1 2
4 Test4 0 0 0
5 Test5 0 0 0
6 Test6 0 0 0
我無法對 value1 的計數和 value2 的計數求和，并在每個唯一 entity_it 的 total_count 中輸出該值。
這是我到目前為止的查詢：
SELECT DISTINCT a.entity_id, a.name
, count(b.value1) AS value1_count, count(b.value2) AS value2_count, sum(2) AS total_count
FROM a
LEFT JOIN b ON a.entity_id = b.entity_id
GROUP BY a.entity_id, a.name
我知道這sum(2) as total_count是不正確的并且沒有得到我想要的。
uj5u.com熱心網友回復：
嘗試這個 ：
WITH list AS
(
SELECT b.entity_id
, count(*) FILTER (WHERE b.value1 IS NOT NULL) OVER () AS value1_count
, count(*) FILTER (WHERE b.value2 IS NOT NULL) OVER () AS value2_count
FROM Table_B AS b
GROUP BY b.entity_id
)
SELECT a.entity_id, a.name
, COALESCE(l.value1_count, 0)
, COALESCE(l.value2_count,0)
, COALESCE(l.value1_count l.value2_count, 0) AS total_count
FROM Table_A AS a
LEFT JOIN list AS l
ON a.entity_id = l.entity_id
uj5u.com熱心網友回復：
SELECT entity_id, a.name
, COALESCE(b.v1_ct, 0) AS value1_count
, COALESCE(b.v2_ct, 0) AS value2_count
, COALESCE(b.v1_ct b.v2_ct, 0) AS total_count
FROM a
LEFT JOIN (
SELECT entity_id, count(value1) AS v1_ct, count(value2) AS v2_ct
FROM b
GROUP BY 1
) b USING (entity_id);
db<>在這里小提琴
先聚合，后加入。這更簡單、更快。看：
- 使用 LEFT JOIN 查詢不回傳計數為 0 的行
count()從不產生NULL. 只有LEFT JOIN可以NULL在此查詢中引入計數值，因此v1_ct和v2_ct要么是 both 要么是NULLboth NOT NULL。因此COALESCE(v1_ct v2_ct, 0)可以。（否則，一個 NULL 將使加法中的另一個被加數無效。）
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/343271.html
標籤：sql PostgreSQL 数数 左连接