本文所用的表信息
学生表stu
字段名 | 数据类型 | 约束条件 |
s_id | varchar(10) | primary key |
s_name | varchar(10) | not null |
s_birth | date | |
s_sex | varchar(10) |
课程表co
字段名 | 数据类型 | 约束条件 |
c_id | varchar(10) | primary key |
c_name | varchar(10) | |
t_id | varchar(10) |
教师表te
字段名 | 数据类型 | 约束条件 |
t_id | varchar(10) | primary key |
t_name | varchar(10) |
成绩表sc
字段名 | 数据类型 | 约束条件 |
s_id | varchar(10) | |
c_id | varchar(10) | |
score | int |
面试题
查询所有学生的课程及分数情况
分析,课程与分数情况,用到学生表及成绩表,两张表都是一维表,将其转化为右图所示的二维表。
第一步,将两张表格连接为一张表格
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;
结果
s_id | 语文 | 数学 | 英语 | 平均分 |
07 | 0 | 89 | 98 | 93.5000 |
01 | 80 | 90 | 99 | 89.6667 |
05 | 76 | 87 | 0 | 81.5000 |
03 | 80 | 80 | 80 | 80.0000 |
02 | 70 | 60 | 80 | 70.0000 |
04 | 50 | 30 | 20 | 33.3333 |
06 | 31 | 0 | 34 | 32.5000 |
08 |
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;
结果
c_id | c_name | 最高分 | 最低分 | 平均分 | 及格率 | 中等率 | 优良率 | 优秀率 |
01 | 语文 | 80 | 31 | 64.5000 | 0.0000 | 0.2500 | 0.2500 | 0.0000 |
02 | 数学 | 90 | 30 | 72.6667 | 0.1250 | 0.0000 | 0.3750 | 0.1250 |
03 | 英语 | 99 | 20 | 68.5000 | 0.0000 | 0.0000 | 0.2500 | 0.2500 |
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;
结果
c_id | c_name | [0-60]所占百分比 | [60-70]所占百分比 | [70-85]所占百分比 | [85-100]所占百分比 |
01 | 语文 | 0.3333 | 0.1667 | 0.6667 | 0.0000 |
02 | 数学 | 0.3333 | 0.1667 | 0.1667 | 0.5000 |
03 | 英语 | 0.3333 | 0.0000 | 0.3333 | 0.3333 |
-- 数据STUDIO --