在 MySQL / SQL 中获取同一行的条件计数

Question

假设我有一个表 Foo，看起来像这样：

ID  | Name  | Gender  | Team
1   | Bob   | Male    | A
2   | Amy   | Female  | A
3   | Cat   | Female  | B
4   | Dave  | Male    | B
5   | Evan  | Male    | B

如果我想获取同一行每队的男性和女性人数列表，我该怎么做？

我知道我可以 SELECT COUNT(Name) as "#", Team, Gender FROM foo GROUP BY Team, Gender，这对于大多数用途来说都很好。

但这会让我每个团队有 2 行，如下所示，这可能会很痛苦。

#  Team Gender
1 | A | Male
1 | A | Female
1 | B | Female
2 | B | Male

我如何构建查询以使它们出现在同一行？

IE，

Team | Males | Females
A    |   1   | 1
B    |   2   | 1

Answer 1

select team,
 SUM(case when gender='Male' then 1 else 0 end) Male,
 SUM(case when gender='Female' then 1 else 0 end) Female
from tbl
group by team

对于评论

现在想象一下，在“分数”列中，每一行都有一个与其关联的任意数字分数。我如何获得“男性积分”和“女性积分”？那是SUM（当性别=“男性”时选择点，否则0结束）“男性点”

你很接近。答案是

select team,
 SUM(case when gender='Male' then 1 else 0 end) Male,
 SUM(case when gender='Male' then points else 0 end) `Male Points`,
 SUM(case when gender='Female' then 1 else 0 end) Female,
 SUM(case when gender='Female' then points else 0 end) `Female Points`
from tbl
group by team

Answer 2

我正在寻找的模式是自连接；在我看来，语法和逻辑比 CASE 模式更优雅。

具体来说，

SELECT Males.Team, COUNT(Males.id) as "Males", SUM(Males.Points) as "Male Points", 
       COUNT(Females.id) as "Females", SUM(Females.Points) as "Female Points" 
FROM scores as Males 
LEFT JOIN scores as Females ON Males.Team=Females.Team AND Females.Gender="Female" 
WHERE Males.Gender="Male" 
GROUP BY Team

我想要在不同列中进行的分组不是 case 语句，而是被拆分为同一个表的自己的副本。然后，您将男性玩家表与团队中的女性玩家表合并，然后按团队进行分组。