表 student 有字段：name，gender，classId
结果展示：
classId 男 女
1 1 1
「原创声明：保留所有权利，禁止转载」
如果觉得我的文章对您有用，请随意打赏。您的支持将鼓励我继续创作！
为啥不用 count 呢？
SELECT classid ,
SUM(IF(gender ='男',1,0)) as '男',
SUM(IF(gender ='女',1,0)) as '女'
FROM student
GROUP BY classid;
case when 写法，时间换空间，牺牲 CPU 时间用于判断和计算
left join 写法，空间换时间，两个查询结果在内存中 sort
附 SQL：
SELECT
a.classid,
IFNULL(a.male_num, 0) AS male_num,
IFNULL(b.famale_num, 0) AS famale_num
FROM
(
(SELECT
classid,
COUNT(*) AS male_num
FROM
student
WHERE gender = 'M' GROUP BY classid) a
LEFT JOIN
(SELECT
classid,
COUNT(*) AS famale_num
FROM
student
WHERE gender = 'F' GROUP BY classid) b ON a.classid = b.classid
)
GROUP BY a.classid ;
select classId,
sum(case when gender='男' then 1 else 0 end) as '男',
sum(case when gender='女' then 1 else 0 end) as '女'
from student
group by classId
由于在服务端是有数据持久化的，因此只要在内存中找到这张表，然后直接查出人数即可
select classId, (sum(CASE WHEN gender = '男' THEN
num ELSE 0 END)) as 男,
(sum(CASE WHEN gender = '女' THEN
num ELSE 0 END)) as 女
from (
select classId, gender, 1 as num from class_user
) b
group by classId
自顶