mysql查询学生的不同科目的分数到一行

数据表：

学生表：

成绩表：

select a.id as 学号, a.name as 姓名, 
(case when b.kemu='语文' then score else 0 end) as 语文,
(case when b.kemu='数学' then score else 0 end) as 数学,
(case when b.kemu='英语' then score else 0 end) as 英语
from student a, grade b
where a.id = b.id

SELECT s.id, s.name, 
max(case when g.kemu='语文' then score else 0 end) as 语文,
max(case when g.kemu='数学' then score else 0 end) as 数学,
max(case when g.kemu='英语' then score else 0 end) as 英语,
sum(score) as 总分,
avg(score) as 平均分
from student s LEFT JOIN grade g ON  s.id = g.s_id GROUP BY s.id

注意：本文归作者所有，未经作者允许，不得转载