mysql多表分组查询

前置条件

成绩表.png

1、多表分组查询学生各科的成绩、总分、平均分

select user.id as 学号, username as 姓名,
sum(case when grade.kemu='语文' then score else 0 end) as 语文,
sum(case when grade.kemu='数学' then score else 0 end) as 数学,
sum(case when grade.kemu='英语' then score else 0 end) as 英语,
sum(grade.score) as 总分,
AVG(grade.score) as 平均分
from user  JOIN grade 
on  user.id = grade.user_id 
GROUP BY user.id;

分组查询结果

统计平均分大于70分

HAVING AVG(grade.score) > 70;

2、统计各科及格人数和不及格人数

SELECT kemu as 科目, count(*) as 总人数,
SUM(CASE WHEN score>=60 THEN 1 ELSE 0 END) as 及格人数,
SUM(CASE WHEN score>=60 THEN 0 ELSE 1 END) as 不及格人数
FROM grade
GROUP BY kemu; 

查询结果

3、左右连接的区别

left join(左联接) 返回包括左表中的所有记录和右表中联结字段相等的记录
right join(右联接) 返回包括右表中的所有记录和左表中联结字段相等的记录
inner join(等值连接) 只返回两个表中联结字段相等的行