已知条件:
1. 学生表,字段为 学号和姓名
2. 课程表,字段为科目ID和科目名称
3. 成绩表 ,字段为学生学号,科目ID,成绩
现要得到如下查询结果
解答:
1. 先对score 表进行行转列,将科目名称作为列名,成绩作为数据进行展示。
select
sid,
max(case when cid = '1' then score else 0 end ) as '语文',
max(case when cid = '2' then score else 0 end ) as '数学',
max(case when cid = '3' then score else 0 end ) as '英语'
from score
group by sid
得到的结果如下
其中 max 函数配合case when使用,例如当cid=1时取score,其他科目成绩设为0,再取最大值就得到了语文成绩的值,最后将列命名为 '语文',以此类推。
group by sid 是要以学生学号进行分组,否则只能查出一条数据。
2. 得到上一步的结果后其实很容易就想到再连接学生表查询即可
select
stu.sid,
stu.sname,
max(case when cid = '1' then score else 0 end ) as '语文',
max(case when cid = '2' then score else 0 end ) as '数学',
max(case when cid = '3' then score else 0 end ) as '英语'
from score sco
left join student stu on stu.sid = sco.sid
group by sid
最终得到要求的结果
补充:查询出 语文成绩高于数学成绩的记录
select
stu.sid,
stu.sname,
max(case when cid = '1' then score else 0 end ) as '语文',
max(case when cid = '2' then score else 0 end ) as '数学',
max(case when cid = '3' then score else 0 end ) as '英语'
from score sco
left join student stu on stu.sid = sco.sid
group by sid
having `语文` > `数学`
此处使用 having 对分组后的数据进行过滤。
得到如下结果