mysql - [SQL]如何统计每班男女生人数？

Question

表User有字段：name，sex，classId，比如有如下记录：
a, 男， 1
b, 男， 1
c, 女， 1
d, 女， 1
e, 男， 2
f, 男， 2

要求查出如下这样的记录（每个班男、女各有几人）：
classId 男女
1 2 2
2 1 1

请问要怎样写？
如果只是查出每个班的男生人数就会：
SELECT class, COUNT(*) AS '男' FROM t_uu WHERE sex='男' GROUP BY class;

ringa_lee · Answer

Command:

SELECT class_id, SUM(sex = '男') AS male, SUM(sex = '女') AS female FROM student
GROUP BY class_id

Result:

Original data:

select * from student;

PHP中文网 · Answer

The data is wrong, isn’t it? If the classid is 2, there should be only 2 boys o(╯□╰)o

黄舟 · Answer

My answer is based on the original poster’s knowledge, and of course mine too:
First find out the situation of boys, such as t1:

|class_id|male|
|:---:|:---:|
|1|2|
|2|1|
Then find out the girl’s situation, such as t2:

class_id	female
1	2
2	1

Finally, chain the two watches together: select t1.class_id,male,female from t1,t2 where t1.class_id = t2.class_id

I haven’t tried it, but it probably means the right thing