在MySQL/SQL中将条件计数添加到同一行 [英] Getting conditional counts on to the same row in MySQL / SQL

问题描述

假设我有一张桌子Foo，看起来像这样:

Suppose I have a table, Foo, that looks like this:

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

如果我想在同一行上获得每个团队的男性和女性人数列表，我该怎么做?

If I wanted to get a list of the number of males and females per team on the same row, how would I do that?

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

I know I could do SELECT COUNT(Name) as "#", Team, Gender FROM foo GROUP BY Team, Gender, and that's fine for most purpose.

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

But that would give me 2 rows per team, like below, and that can be a pain.

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

如何构造查询以使其出现在同一行?

How could I structure the query such that they appear on the same row?

即

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

推荐答案

我正在寻找的模式是自我加入"；在我看来，语法和逻辑比CASE模式更优雅.

The pattern I was looking for was a Self-Join; the syntax and logic is, in my mind, more elegant then the CASE pattern.

具体地说，

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语句拆分为同一张表的自己的副本.然后，您将男性球员表与团队中的女性球员表一起加入，然后按团队分组.

Instead of case statements, the groupings I want in different columns get split into their own copies of the same table. You then join the table of Male players with a Table of Female players on the Team, and then group by the Team.

这篇关于在MySQL/SQL中将条件计数添加到同一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！