职业经验 # 每日一道面试题 # 统计每个班的男生女生分别有多少人?

金主 for 求职面试圈 · October 16, 2017 · Last by 思追 replied at March 04, 2018 · Last modified by admin 恒温 · 1670 hits

表student有字段:name,gender,classId

结果展示:
classId 男 女
1 1 1

如果觉得我的文章对您有用,请随意打赏。您的支持将鼓励我继续创作!
共收到 8 条回复 时间 点赞

自顶


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

由于在服务端是有数据持久化的,因此只要在内存中找到这张表,然后直接查出人数即可

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

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(IF(gender ='男',1,0)) as '男',
SUM(IF(gender ='女',1,0)) as '女'
FROM student
GROUP BY classid;
liu1993 回复

你这个不错

为啥不用count呢?

需要 Sign In 后方可回复, 如果你还没有账号请点击这里 Sign Up