如何根據分組的列值回傳總行數，下面的代碼可以作業并列印具有專案所有權的不同團隊，但 total_member 計數不正確，比實際高出大約 10 倍，看起來每個 CASE WHEN 乘以行計數。
這是我的代碼：
SELECT
team.team_name,
team_rank.points,
SUM(CASE WHEN items.item_id = 1 THEN items.count END) AS item1,
SUM(CASE WHEN items.item_id = 2 THEN items.count END) AS item2,
SUM(CASE WHEN items.item_id = 3 THEN items.count END) AS item3,
SUM(CASE WHEN items.item_id = 4 THEN items.count END) AS item4,
COUNT(member.team_id) AS total_members
FROM
member
INNER JOIN
items ON member.obj_Id = items.owner_id
INNER JOIN
team ON team.team_id = member.team_id
JOIN
team_rank ON team.team_id = team_rank.team_id
GROUP BY
member.team_id
下面是當前結果，專案計算是正確的，但實際上一隊只有 19 人，二隊有 5 人，所以乘了很多倍。
編輯：
請按要求查看在小提琴中上傳的示例資料庫資料。 https://www.db-fiddle.com/f/gwWCEa3vaPs1JTgBnwk1wM/0
uj5u.com熱心網友回復：
在內部聚合member以獲取total_members結果并將其加入查詢：
SELECT
team.team_name,
team_rank.points,
SUM(CASE WHEN items.item_id = 1 THEN items.count END) AS item1,
SUM(CASE WHEN items.item_id = 2 THEN items.count END) AS item2,
SUM(CASE WHEN items.item_id = 3 THEN items.count END) AS item3,
SUM(CASE WHEN items.item_id = 4 THEN items.count END) AS item4,
c.total_members
FROM member
INNER JOIN (SELECT team_id, COUNT(team_id) total_members FROM member GROUP BY team_id) c
ON c.team_id = member.team_id
INNER JOIN items ON member.obj_Id = items.owner_id
INNER JOIN team ON team.team_id = member.team_id
INNER JOIN team_rank ON team.team_id = team_rank.team_id
GROUP BY member.team_id;
請參閱演示。
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/516141.html
上一篇：Pandas-基于索引的左外連接