主要用到isum函数,if()三元, case when then else end
参考答案
#查询年级的男女生数
SELECT g.grade_name 年级名,
sum(if(sex='女',1,0)) as 女生数,
sum(if(sex='男',1,0)) as 男生数
from students s LEFT JOIN Grade g
ON s.grade_id = g.id
GROUP BY g.grade_name
#每个老师分别带了每个年级男女多少名
SELECT t.teacher_name,
g.grade_name,
sum(if(sex='女',1,0)) as 女生数,
sum(if(sex='男',1,0)) as 男生数
FROM students s
LEFT JOIN teacher t ON s.teacher_id = t.id
LEFT JOIN Grade g ON s.grade_id = g.id
GROUP BY t.teacher_name,g.grade_name