我的 postgesql 資料庫中有三個表。
- 城市：id，名稱
- 工人：id、city_id、姓名
- 供應商：id、city_id、姓名
我想創建一個 sql 查詢來獲取同一個城市的兩個表的總和，例如：
City w_count s_count
A 5 2
B 8 4
C 9 7
E 9 0
E 0 1
sql是
select c.name, w.total, s.total
from (select city_id, count(*) as total from workers ) w
left join (select city_id, count(*) as total from suppliers) s
on w.city_id = s.city_id,
city as c
where c.id = w.city_id and c.id = s.city_id
如果任何城市都沒有供應商而城市中有工人，則該行缺失。但是單元格應該是0。
我該怎么做這個查詢？
uj5u.com熱心網友回復：
您應該從city表中加入 2 個計數子查詢：
SELECT c.Name AS City,
COALESCE(w.total, 0) AS w_count,
COALESCE(s.total, 0) AS s_count
FROM city c
LEFT JOIN
(
SELECT city_id, COUNT(*) AS total
FROM workers
GROUP BY city_id
) w
ON w.city_id = c.id
LEFT JOIN
(
SELECT city_id, COUNT(*) AS total
FROM suppliers
GROUP BY city_id
) s
ON s.city_id = c.id;
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/448469.html
標籤：sql PostgreSQL