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

本文所用的表信息

学生表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

面试题

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

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

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

代码语言：javascript复制

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

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

语句

代码语言：javascript复制

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;

另外两种写法

代码语言：javascript复制

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排序。

代码语言：javascript复制

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

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

代码语言：javascript复制

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]及所占百分比

代码语言：javascript复制

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

key varchar

0 人点赞