又一道经典SQL笔试题详解 (文末有福利)

大家好，我是云朵君！

一个数据工作者面试数据相关岗位，SQL查询语句是必不可少的笔试环节，今天云朵君给大家带来了某厂一道面试题，附上参考答案，希望能够帮到大家！

👆点击关注｜设为星标｜干货速递👆

◎ 写出学生没有参加考试的课程：学生、姓名、班级、课程
◎ 找出每门课程的前三名:课程、第一名（姓名+分数）、第二名（姓名+分数）、第三名（姓名+分数）
◎ 找出0611班所有男生的成绩：姓名、语文、数学、英语、物理、化学、总分

课程表 t_lesson

字段名称：课程id，课程名称

学生表 t_stu_profile

字段名称：学生id，学生姓名，性别，年龄，班级

分数表 t_score

字段名称：学生id，课程id，分数

参考解答

※ 写出学生没有参加考试的课程：学生、姓名、班级、课程

☆ 解析：

① 学生、姓名、班级、课程 -- 需要查出这四个字段，分别在学生表课程表里。因为需要所有学生及其对应的所有课程，因此首先两表做笛卡尔积：

SELECT * FROM t_stu_profile,t_lesson

② 没有参加考试的课程 -- 没有考试就是没有成绩，因此需要用到成绩表。将上面的得到的中间表格，通过stu_id
和lesson_id
为链接字段，将成绩表链接上去。

③ 最后通过筛选条件WHERE
，筛选字段score
为null
的信息

SELECT t.stu_id,stu_name,class_id,lesson_name
FROM (SELECT * FROM t_stu_profile,t_lesson) t
LEFT JOIN t_score ON t_score.stu_id = t.stu_id AND t_score.lesson_id = t.lesson_id
WHERE score IS null;

☆ 结果

※ 找出每门课程的前三名:课程、第一名（姓名+分数）、第二名（姓名+分数）、第三名（姓名+分数）

☆ 解析：

① 课程、姓名、分数 分别在课程表，学生表及成绩表里，因此需要先将这三张表格链接起来。并且需要在课程中按照分数排名，因此需要用到窗口函数。

SELECT lesson_name,stu_name,score, 
RANK() OVER(PARTITION BY lesson_name ORDER BY score DESC)排名
FROM t_lesson 
LEFT JOIN t_score ON t_score.lesson_id = t_lesson.lesson_id
LEFT JOIN t_stu_profile ON t_score.stu_id = t_stu_profile.stu_id;

② 姓名+分数 -- 要求输出姓名+分数
形式，所有需要通过按照课程聚合GROUP BY
，运用 GROUP_CONCAT()
 函数将姓名和分数通过+
组合。

③ 第一名（姓名+分数）、第二名（姓名+分数）、第三名（姓名+分数） -- 这里涉及到使用一维表转二维表操作。可以参考

SELECT lesson_name 课程,
      GROUP_CONCAT(IF(排名=1,CONCAT(stu_name,'+',score),null)) 第一名,
      GROUP_CONCAT(IF(排名=2,CONCAT(stu_name,'+',score),null)) 第二名,
      GROUP_CONCAT(IF(排名=3,CONCAT(stu_name,'+',score),null)) 第三名
FROM
(SELECT lesson_name,stu_name,score, 
 RANK() OVER(PARTITION BY lesson_name order BY score DESC)排名
FROM t_lesson 
LEFT JOIN t_score ON t_score.lesson_id = t_lesson.lesson_id
LEFT JOIN t_stu_profile ON t_score.stu_id = t_stu_profile.stu_id) t 
GROUP BY 课程;

☆ 结果

※ 找出0611班所有男生的成绩：姓名、语文、数学、英语、物理、化学、总分

☆ 解析：

① 0611班所有男生 -- 通过条件筛选WHERE
，按照条件gender = 'M' AND class_id = 0611
筛选即可。

② 总分 -- 其中总分可按照stu_name
聚合GROUP BY
操作，并使用SUM()
求和即可。

③ 语文、数学、英语、物理、化学 -- 因为课程名称在课程表，分数在成绩表，需要链接两表格。并且课程表中是一维表，需要运用到一维表转二维表。

④ 姓名 -- 在学生表中，需要将学生表同上面得到的表链接。

SELECT stu_name,
        SUM(CASE WHEN lesson_name = '语文' THEN score ELSE  0 END) 语文,
        SUM(CASE WHEN lesson_name = '数学' THEN score ELSE  0 end) 数学,
        SUM(CASE WHEN lesson_name = '英语' THEN score ELSE  0 END) 英语,
        SUM(CASE WHEN lesson_name = '物理' THEN score ELSE  0 end) 物理,
        SUM(CASE WHEN lesson_name = '化学' THEN score ELSE  0 END) 化学,
        SUM(score) 总分
FROM t_stu_profile LEFT JOIN t_score ON t_score.stu_id = t_stu_profile.stu_id
LEFT JOIN t_lesson ON t_score.lesson_id = t_lesson.lesson_id
WHERE gender = 'M' AND class_id = 0611
GROUP BY stu_name;

☆ 结果

建表与导数

为方便小伙伴们操作联系，数据库建表和导入数据代码给你贴出来了。

# create database STUDIO;
use STUDIO;

create table t_stu_profile(
stu_id varchar(10) primary key,
stu_name varchar(10),
gender varchar(10),
age int,
class_id varchar(10) not null
);

create table t_lesson(
lesson_id varchar(10) primary key,
lesson_name varchar(10)
);

create table t_score(
stu_id varchar(10) references t_stu_profile(stu_id),
lesson_id varchar(10) references t_lesson(lesson_id),
score int
);

insert into t_stu_profile values('001','郭东','F',16,'0611'),
								('002','李西','M',18,'0612'),
                                ('003','张北','F',16,'0613'),
                                ('004','钱南','M',17,'0611'),
                                ('005','王五','F',17,'0614'),
                                ('006','赵七','F',16,'0615');
                                
insert into t_lesson values('L001','语文'),
						   ('L002','数学'),
                           ('L003','英语'),
                           ('L004','物理'),
                           ('L005','化学');
                            
insert into t_score values ('001','L001',90),
						   ('001','L002',86),
                           ('002','L001',84),
                           ('002','L004',75),
                           ('003','L003',85),
                           ('004','L005',98);
 
 select * from t_stu_profile;
 select * from t_lesson;
 select * from t_score;

SQL知识手册思维导图.pdf

左侧为全视图，右侧为局部放大图。

思维导图 高清PDF版 下载方式

由于图片大小限制，不能清晰展示，云朵君特地准备了高清版PDF版供大家下载：

扫描下方二维码添加云朵君微信，请务必备注【SQL思维导图】，免费获取！

OK，今天的分享就到这里啦！

没看够？点赞在看走起来～后续更精彩～