MySQL高频面试题：一维表转二维表

本文所用的表信息

学生表stu

课程表co

教师表te

成绩表sc

面试题

查询所有学生的课程及分数情况

分析，课程与分数情况，用到学生表及成绩表，两张表都是一维表，将其转化为右图所示的二维表。

第一步，将两张表格连接为一张表格

SELECT stu.*,c_id,score
FROM stu 
LEFT JOIN sc ON stu.s_id=sc.s_id;

第二步，将上面得到的结果转换为二维表

语句

SELECT stu.s_id,stu.s_name,
		SUM(if(c_id='01',score,0)) '01',
		SUM(if(c_id='02',score,0)) '02',
        SUM(if(c_id='03',score,0)) '03'
FROM stu 
LEFT JOIN sc ON stu.s_id=sc.s_id
GROUP BY stu.s_id;

另外两种写法

SELECT stu.s_id,stu.s_name,
		SUM(CASE WHEN c_id='01' THEN score ELSE 0 END) '01',
		SUM(CASE WHEN c_id='02' THEN score ELSE 0 END) '02',
        SUM(CASE WHEN c_id='03' THEN score ELSE 0 END) '03'
FROM stu 
LEFT JOIN sc ON stu.s_id=sc.s_id
GROUP BY stu.s_id;

SELECT stu.s_id,stu.s_name,
		SUM((c_id='01')*score) '01',
        SUM((c_id='02')*score) '02',
        SUM((c_id='03')*score) '03'
FROM stu 
LEFT JOIN sc ON stu.s_id=sc.s_id
GROUP BY stu.s_id;

练习

1、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

分析，"有学生的所有课程的成绩"，需要用到学生表、成绩表。"平均成绩"需要按学生聚合GROUP BY
并求平均值AVG
。"按平均成绩从高到低显示"需要用ORDER BY
排序。

SELECT stu.s_id,
		SUM((c_id='01')*score) '语文',
        SUM((c_id='02')*score) '数学',
        SUM((c_id='03')*score) '英语',
		AVG(score) '平均分'
FROM  stu 
LEFT JOIN sc ON stu.s_id=sc.s_id
GROUP BY stu.s_id
ORDER BY AVG(score) DESC;

结果

2、查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率，及格为：>=60，中等为：70-80，优良为：80-90，优秀为：>=90

分析，从需要显示的列看出需要用到课程表和成绩表。各科成绩最高分、最低分和平均分，需要按照课程聚合并求平均分。

SELECT sc.c_id,c_name,max(score) 最高分,min(score) 最低分,AVG(score) 平均分,
		SUM(score>=60 and score<70)/(SELECT COUNT(s_id) FROM stu) 及格率,
        SUM(score>=70 and score<80)/(SELECT COUNT(s_id) FROM stu) 中等率,
        SUM(score>=80 and score<90)/(SELECT COUNT(s_id) FROM stu) 优良率,
        SUM(score>=90)/(SELECT COUNT(s_id) FROM stu) 优秀率
FROM sc 
LEFT JOIN co ON sc.c_id=co.c_id
GROUP BY sc.c_id;

结果

3、统计各科成绩各分数段人数：课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比

SELECT sc.c_id, c_name,
       SUM(score BETWEEN 0 AND 60)/COUNT(sc.c_id) AS '[0-60]所占百分比',
       SUM(score BETWEEN 60 AND 70)/COUNT(sc.c_id) AS '[60-70]所占百分比',
       SUM(score BETWEEN 70 AND 85)/COUNT(sc.c_id) AS '[70-85]所占百分比',
       SUM(score BETWEEN 85 AND 100)/COUNT(sc.c_id) AS '[85-100]所占百分比'
FROM sc 
LEFT JOIN co ON sc.c_id=co.c_id
GROUP BY sc.c_id;

结果