-- 2. In one table, show how many private topics, admins, and standard users each organization has.
SELECT organizations.name, COUNT(topics.privacy) AS private_topic, COUNT(users.type) AS user_admin, COUNT(users.type) AS user_standard
FROM organizations
LEFT JOIN topics
ON organizations.id=topics.org_id
AND topics.privacy='private'
LEFT JOIN users
ON users.org_id=organizations.id
AND users.type='admin'
LEFT JOIN users
ON users.org_id=organizations.id
AND users.type='standard'
GROUP BY organizations.name
;
org_id 是實作用戶表和主題表的外鍵。它只通過計算管理員或標準用戶的數量并將其放入每列中的所有行來不斷給我錯誤的結果。任何幫助都非常感謝，因為我已經堅持了一段時間了！
所以，當我按照你說的那樣做時，我收到一個錯誤，即不能多次指定用戶表。我將代碼更新為您所說的撰寫方式，但仍然沒有。他們也確實沒有給我任何示例資料，但我只是進行了一些查詢，并看到了私人主題的次數，例如，在主題表的隱私列中。當我沒有像我說的那樣收到這個錯誤時，連接似乎會覆寫自己，其中所有列的每一行都與最后一個連接相同。
uj5u.com熱心網友回復：
在我看來，主題和用戶沒有關系。您只是想在單個查詢中獲得結果。還有其他可能更好的方法來實作這一點，但我認為這將解決您已經擁有的問題（假設每個表都有 id 列。）
SELECT
organizations.name,
COUNT(DISTINCT topics.id) AS private_topic,
COUNT(DISTINCT users.id) FILTER (WHERE users.type = 'admin') AS user_admin,
COUNT(DISTINCT users.id) FILTER (WHERE users.type = 'standard') AS user_standard`
FROM organizations
LEFT JOIN topics
ON organizations.id = topics.org_id AND topics.privacy = 'private'
LEFT JOIN users
ON users.org_id = organizations.id
GROUP BY organizations.name;
我建議這是一種更直接的方式：
SELECT
min(o.name) as "name",
(
select count(*) from topics t
where t.org_id = o.id AND t.privacy = 'private'
) as private_topics,
(
select count(*) from users u
where u.org_id = o.id and u.type = 'admin'
) AS user_admin,
(
select count(*) from users u
where u.org_id = o.id and u.type = 'standard'
) AS user_standard
FROM organizations o
GROUP BY o.id;
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/337127.html
標籤：sql PostgreSQL