我有一个人表:
| id | name | age (18-20) | gender |
------------------------------------
| 1 | Kyle | 20 | male |
| 2 | Jane | 19 | female |
| 3 | Brian| 19 | male |
| 4 | Chris| 19 | male |
| 5 | Mary | 19 | female |
| 6 | Kate | 20 | female |
我想做一个SELECT,在那里我将每个性别放在他们自己的列中,行应该显示年龄计数,如下所示:
| male | female |
-----------------
| 0 | 0 |
| 2 | 2 |
| 1 | 1 |
第一排显示有两名18岁的男性和一名女性。秒显示19等。我只想理解逻辑,所以顺序(ASC、DESC(和类似的细节并不重要。
简单分组和条件聚合:
select age
, count(case when gender = 'male' then 1 end) male
, count(case when gender = 'female' then 1 end) female
from person
group by age
然后从评论:
是否可以始终使其返回所有3个年龄段的结果。这意味着如果没有人是18岁,它仍然应该返回所有3行0
select ages.age
, count(case when gender = 'male' then 1 end) male
, count(case when gender = 'female' then 1 end) female
from (VALUES (18) , (19) , (20)) ages(age)
left join person p
on ages.age = p.age
group by ages.age