SQL新手，一直在谷歌搜索無濟于事。這是架構：
"users"
Column | Type |
--------------------- --------------------------
id | text |
name | text |
title | text |
org_id | text |
type. | text |
"organizations"
Column | Type |
-------------------------------- --------------------------
id | text |
name | text |
"posts"
Column | Type |
------------------- --------------------------
id | text |
title | text |
content | jsonb[] |
owner_id | text |
org_id | text |
is_public | boolean |
我的目標是在一張表中顯示每個組織有多少私人主題、管理員和標準用戶，如下所示：
Org | Users | Admins | Private Posts
---------- ------- ------- ---------------
Org1 | 56 | 10 | 22
Org2 | 111 | 10 | 34
現在，我只得到這個：
Org | Count | Type | Private Posts
---------- ------- ------- ---------------
Org1 | 10 | admin | 22
Org2 | 111 | user | 34
Org1 | 56 | user | 22
Org2 | 10 | admin | 34
使用：
SELECT t1.id as "Org", t1.cnt as "Count", t1.type as "Type", t2.cnt as "Private Posts" from
(SELECT COUNT(u.type) as "cnt", u.type as "type", o.id FROM "users" AS u JOIN
"organizations" AS o ON o.id=u.org_id GROUP BY u.type, o.id) as t1 join
(SELECT COUNT(org_id) as "cnt", org_id from posts WHERE is_public = False group
by org_id) as t2 on t2.org_id = t1.id;
我基本上嘗試加入用戶和組織并根據組織和用戶型別（t1）計數，然后統計帖子中的公共帖子（t2），并嘗試根據組織id加入t1和t2。任何幫助表示贊賞。
uj5u.com熱心網友回復：
考慮一個稱為條件聚合的概念，它根據條件邏輯將結果轉置到所需的寬格式列。Postgres 維護FILTER對此類查詢的有用性，但也可以使用CASE與其他 RDBMS 共享的陳述句：
SELECT o.id AS "Org",
COUNT(*) FILTER(WHERE u.type = 'user') AS "Users",
COUNT(*) FILTER(WHERE u.type = 'admin') AS "Admins",
COUNT(*) FILTER(WHERE is_public = False) AS "Private Posts"
FROM users u
JOIN organizations o
ON o.id = u.org_id
JOIN posts p
ON o.id = p.org_id
GROUP BY o.id;
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/407034.html
標籤：