SQL查询结果行转列问题

1.数据准备

select * from student;

select * from score;

2.要求：查询出有成绩学生的各科成绩信息和总分，并按照总分降序排列，显示效果如下

①关联自身表查询

SELECT
    a. NAME '姓名',
    b.score '语文',
    c.score '数学',
    d.score '英语',
    (b.score + c.score + d.score) '总分'
FROM
    student a
JOIN score b ON a.id = b.uid
JOIN score c ON a.id = c.uid
JOIN score d ON a.id = d.uid
WHERE
    b. SUBJECT = '语文'
AND c. SUBJECT = '数学'
AND d. SUBJECT = '英语'
ORDER BY
    (b.score + c.score + d.score) DESC;

②使用case关键字优化查询

select a.`name`,
sum(case when subject='语文' then b.score end) as '语文',
sum(case when subject='数学' then b.score end) as '数学',
sum(case when subject='英语' then b.score end) as '英语',
sum(b.score) as '总分'
from student a
join score b on a.id = b.uid
GROUP BY a.name
order by sum(b.score) desc;

3.总结

在数据量相对较大的情况，推荐使用case关键字优化查询，避免多次关联自身表查询。