sql 经典50题

您所在的位置：网站首页 › groupby能去重是吧 › sql 经典50题

sql 经典50题

2023-04-21 19:57| 来源: 网络整理| 查看: 265

这篇文写了挺久了，有一位细心的小伙伴 @滑稽发现了一个错误，很感谢有人认真的在看，我重新修改了一下！

很久之前我写过一篇学习sql的基本操作，但是自己好像在sql上面有点停滞了。最近有和一位朋友聊过如何学习sql，朋友给我的回复是多写代码。我深以为然于是就找到了这个经典的50题。

虽然这个经典50题网上有很多人分享过，但是，只有自己实际操作过以后才能真正的理解其中的坑，也才能够加深对知识的理解吧。我希望自己能够做一套很详细的解析。

解析包含：1解题思路，2相关知识，3 实际语句实现；

申明：我只是一个初学者，还有很多不懂的地方，所以如果您发现了错误，希望能在下边提出来，万分感谢！

如果你觉得我很走心，也欢迎点赞！

首先建表，主要有四个表，学生表（Student），课程表（Course），教师表（Teacher），以及成绩表（SC）

在分别介绍一下每个表的字段学生表（Student ）有四个字段 sid--学生id，sname--学生姓名，sage--学生年龄，ssex--学生性别

课程表（Course）有三个字段，cid--课程id，cname--课程名，tid--教师id

教师表（Teacher）有两个字段，tid--教师id，tname--教师姓名

成绩表（SC）有三个字段，sid--学生id，cid--课程id，score--成绩

我们用脑图的方式绘制出来，如下所示

学生表结构

课程表结构

教师表结构

成绩表结构

各个表之间的关系如下，Student和SC表通过学生id（sid）来连接，Course和Teacher表通过教师id（tid）连接，SC和Course表通过课程id（cid）来连接。

create table Student(sid varchar(10),sname varchar(10),sage datetime,ssex nvarchar(10)); insert into Student values('01' , '赵雷' , '1990-01-01' , '男'); insert into Student values('02' , '钱电' , '1990-12-21' , '男'); insert into Student values('03' , '孙风' , '1990-05-20' , '男'); insert into Student values('04' , '李云' , '1990-08-06' , '男'); insert into Student values('05' , '周梅' , '1991-12-01' , '女'); insert into Student values('06' , '吴兰' , '1992-03-01' , '女'); insert into Student values('07' , '郑竹' , '1989-07-01' , '女'); insert into Student values('08' , '王菊' , '1990-01-20' , '女'); create table Course(cid varchar(10),cname varchar(10),tid varchar(10)); insert into Course values('01' , '语文' , '02'); insert into Course values('02' , '数学' , '01'); insert into Course values('03' , '英语' , '03'); create table Teacher(tid varchar(10),tname varchar(10)); insert into Teacher values('01' , '张三'); insert into Teacher values('02' , '李四'); insert into Teacher values('03' , '王五'); create table SC(sid varchar(10),cid varchar(10),score decimal(18,1)); insert into SC values('01' , '01' , 80); insert into SC values('01' , '02' , 90); insert into SC values('01' , '03' , 99); insert into SC values('02' , '01' , 70); insert into SC values('02' , '02' , 60); insert into SC values('02' , '03' , 80); insert into SC values('03' , '01' , 80); insert into SC values('03' , '02' , 80); insert into SC values('03' , '03' , 80); insert into SC values('04' , '01' , 50); insert into SC values('04' , '02' , 30); insert into SC values('04' , '03' , 20); insert into SC values('05' , '01' , 76); insert into SC values('05' , '02' , 87); insert into SC values('06' , '01' , 31); insert into SC values('06' , '03' , 34); insert into SC values('07' , '02' , 89); insert into SC values('07' , '03' , 98);

1.查询"01"课程比"02"课程成绩高的学生的信息及课程分数

解题思路：要查询的是两个课程的成绩，而且还要显示学生的信息。所以需要用到两张表，SC，Student这两张表。

问题拆分：（1）怎么查找两个课程的成绩呢？（2）如何把课程表和学员信息表连接起来呢？

那么用到哪些知识呢？

（1）子查询（2） join

语句实现：先找到两门课的成绩

-- 课程1的成绩 SELECT sid ,score AS class1 FROM sc WHERE sc.cid = '01'; -- 课程2的成绩 SELECT sid,score AS class2 FROM sc WHERE sc.cid = '02';

class1 查询结果

class2 查询结果

两个子查询结束了以后，下一步就是使用join把这两个查询的结果连接起来

SELECT * FROM Student RIGHT JOIN (SELECT t1.sid,class1,class2 FROM -- 这个是要查询两个表的成绩，即课程1大于课程2 (SELECT sid ,score AS class1 FROM sc WHERE sc.cid = '01') AS t1, (SELECT sid,score AS class2 FROM sc WHERE sc.cid = '02') AS t2 WHERE t1.sid = t2.sid AND t1.class1 > t2.class2) r -- 这里是固定的，就是子查询的一个别名的设置 -- 这一步就是把两个表按照sid联结起来 ON Student.sid = r.sid;

最终查询结果

好的，，第一题就解决了

第二题：1.1 查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为 null )

思路：首先要查询所有的学生的选课情况，然后找到选择课程1和选择课程2的学生

解决方法：使用join

选择两个分别选择课程1和课程2的学员表，然后把这两个表join起来

SELECT * FROM(SELECT * FROM sc WHERE sc.cid = '01' ) t1 LEFT JOIN (SELECT * FROM sc WHERE sc.cid = '02') t2 ON t1.sid = t2.sid ;

输出结果

1.2 查询同时存在01和02课程的情况

思路：要求同时选择了01和02课程的情况，则需要使用where链接起来，这一题和上一题相比就是多了一个去除选了01但是没有选择02课程的这一部分

知识点：子查询，where语句

SELECT * FROM (SELECT * FROM sc WHERE sc.cid = '01') AS t1, (SELECT * FROM sc WHERE sc.cid = '02') AS t2 WHERE t1.sid = t2.sid;

执行结果

1.3 查询选择了02课程但没有01课程的情况

思路，要求首先得到选择了02课程的学员，然后剔除掉选择01课程的学员

知识点：子查询，NOT IN

SELECT * FROM sc WHERE sc.sid NOT IN (SELECT sid FROM sc WHERE sc.cid = '01') AND sc.cid = '02';

写好了这个语句以后我觉得自己写的不够清楚，因此我使用了别名

看一下结果:

SELECT * FROM sc WHERE sc.sid NOT IN (SELECT sid FROM sc WHERE sc.cid = '01') AND sc.cid = '02';

执行结果

小结：上面几个题基本可以说是同一类型，要注意子查询的使用，以及join和where的区别

2.查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩

思路：看到这个题的时候第一想法是要使用group by，和having，又因为成绩表（sc）没有学生姓名，所以还要使用join，当然avg肯定是需要的

考察知识：group by，join

SELECT s.sid,sname,AVG(sc.score) FROM student AS s INNER JOIN sc ON s.sid = sc.sid GROUP BY sc.sid HAVING AVG(sc.score) >= 60;

执行结果

还有一种解法，使用子查询

SELECT r.*, s.sname FROM (SELECT sid,AVG(score) FROM sc GROUP BY sid HAVING AVG(score) > 60) r LEFT JOIN student AS s ON s.sid = r.sid;

3.查询在 SC 表存在成绩的学生信息

思路：这题比较简单，就是两张表通过id连接，然后求单独值

知识点：Distinct

SELECT DISTINCT student.* FROM sc,student WHERE student.sid = sc.sid ;

执行结果

4.查询所有同学的学生编号、学生姓名、选课总数、所有课程的成绩总和

解题思路：首先用到两张表格，然后要求选课总数，和成绩总和，那么就需要用到groupby 和sum以及count

知识点：gruop by， sum，join，count

SELECT s.sid,s.sname,COUNT(sc.cid),SUM(score) FROM student AS s INNER JOIN sc ON s.sid = sc.sid GROUP BY sc.sid;

执行结果

5.查询「李」姓老师的数量

解题思路：这题考的是通配符的查询，比如使用like和%

知识点：count，like，%

SELECT COUNT(*) FROM teacher WHERE tname LIKE ('李%');

执行结果

6.查询学过「张三」老师授课的同学的信息

解题思路：要查找张三老师的授课科目，然后通过科目和score表连接，然后再和学生表连接

知识点：就是多重连接 inner join

SELECT s.* FROM student AS s INNER JOIN sc ON s.sid = sc.sid INNER JOIN course AS c ON sc.cid = c.cid INNER JOIN teacher AS t ON t.tid = c.tid WHERE t.tname = '张三';

执行结果

7.查询没有学全所有课程的同学的信息

解题思路：首先利用子查询查询course表查询得到共有几门课，按照groupby的方式求课程数加上having小于查询出来的课程的所有学员信息就好

知识点：子查询，groupby，having，join

SELECT s.*,COUNT(cid) FROM sc RIGHT JOIN student AS s ON sc.sid = s.sid GROUP BY sc.sid HAVING COUNT(cid) = 3);-- 这一句也可以使用子查询

执行结果

8.查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息

解题思路：首先要查询出01同学所学的课程，然后使用cid in 01同学的课程，并排除sid=01的同学

SELECT s.* FROM sc INNER JOIN student AS s ON sc.sid = s.sid WHERE sc.cid IN (SELECT cid FROM sc WHERE sid = '01') AND sc.sid != '01' GROUP BY sc.sid;

执行结果

我这里使用了groupby语句来求取单个学员id，也可以使用distinct关键字来做区分

SELECT DISTINCT s.* FROM sc INNER JOIN student AS s ON sc.sid = s.sid WHERE sc.cid IN (SELECT cid FROM sc WHERE sid = '01') AND sc.sid != '01';

执行结果

这里就有一个知识点就是group by有去除重复值的功能，这个其实不难理解，因为group by就是按照单个组分类，可以理解为按照同一类进行切分

9.查询和" 01 "号的同学学习的课程完全相同的其他同学的信息

解题思路：首先感觉真题很难，想法是在student表的sid在sc的id中，而这个id又和cid相关联，然后再查询结束时，还要排除id=01的情况。反正很复杂。。。

知识点：n重子查询，in的使用

SELECT * FROM student WHERE sid IN (SELECT sid FROM (SELECT * FROM sc AS a WHERE cid IN (SELECT cid FROM sc WHERE sid = 01)) b GROUP BY sid HAVING COUNT(cid) = (SELECT COUNT(cid) FROM sc c WHERE sid = 01)) AND sid != 01 ; SELECT * FROM student WHERE sid IN (SELECT sid FROM (SELECT * FROM sc AS a WHERE cid IN (SELECT cid FROM sc WHERE sid = 01)) b GROUP BY sid HAVING COUNT(cid) = (SELECT COUNT(cid) FROM sc c WHERE sid = 01)) AND sid != 01 ;

10.查询没学过"张三"老师讲授的任一门课程的学生姓名

解题思路：1先要查询出张三老师的教授课程，2然后利用not in 来找到学生的id

知识点：子查询，not in，多重join

SELECT sname FROM student WHERE sid NOT IN ( SELECT sid FROM sc LEFT JOIN course ON sc.cid=course.cid LEFT JOIN teacher ON course.tid=teacher.tid WHERE tname='张三' )

11.查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩

解题思路：题中提到了两门及以上的不及格课程，因此考虑使用Groupby 和 having，题中还提到学生姓名以及平均成绩，考虑到这里还要求出平均成绩，但是平均成绩说明不是很清楚，

知识点：Group by ，having，以及子查询在from子句中的使用

SELECT sname,s.sid,AVG(sc1.score) AS avg_score FROM (SELECT * FROM sc WHERE score =2;

12.检索" 01 "课程分数小于 60，按分数降序排列的学生信息

解题思路：要求01课程小于60的学员信息，然后按照降序排列学员信息

知识点：就是很简单的order by

SELECT s.* FROM sc,student AS s WHERE cid = '01' AND score =60，中等为：70-80，优良为：80-90，优秀为：>=90

要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序

排列

解题思路：这里要把最高分，最低分，平均分，等多个维度，那么就需要使用case when 语句了还有就是函数的使用

知识点：case when 别名的使用，以及函数的使用，和函数的嵌套使用，题目后边还考到了多列排列的使用，先按照desc，后按照asc排列

SELECT cid AS 课程ID, COUNT(sid) AS 课程人数, MAX(score) AS 最高分, MIN(score) AS 最低分, AVG(score) AS 平均分, SUM(及格) / COUNT(sid) AS 及格率, SUM(中等) / COUNT(sid) AS 中等率, SUM(优良) / COUNT(sid) AS 优良率, SUM(优秀) / COUNT(sid) AS 优秀率 FROM (SELECT *, CASE WHEN score >= 60 THEN 1 ELSE 0 END AS 及格, CASE WHEN score >= 70 AND score = 80 AND score = 90 THEN 1 ELSE 0 END AS 优秀 FROM sc) a GROUP BY cid ORDER BY COUNT(sid) DESC, cid ;

特别长，但是知识很简单，就是case when语句的使用

15.按各科成绩进行排序，并显示排名， Score 重复时保留名次空缺

解题思路：两张成绩表连接，然后按照成绩大小做一个排名

知识点：排序相关知识

SELECT a.*, COUNT(a.score) AS 排名 FROM sc AS a LEFT JOIN sc AS b ON a.cid = b.cid AND a.score < b.score GROUP BY a.cid, a.sid ORDER BY a.cid, 排名 ;

15.1 按各科成绩进行行排序，并显示排名， Score 重复时合并名次

解题思路：相比于上一题这里多了一个合并名次的事情，所谓名次合并，意思是两个人的成绩一致按照一个名次来，比如两个成绩一样的排名第一，下一个排名第二的就当做第三名

SELECT a.*, COUNT(b.score)+1 AS 排名 FROM sc AS a LEFT JOIN sc AS b ON a.cid = b.cid AND a.score < b.score GROUP BY a.cid, a.sid ORDER BY a.cid, 排名 ;

16.查询学生的总成绩，并进行排名，总分重复时保留名次空缺

解题思路：和15题差不多，不过我要引入一个用户变量

知识点：用户变量的使用

SELECT a.*, @rank := @rank + 1 AS rank FROM (SELECT sid, SUM(score) FROM sc GROUP BY sid ORDER BY SUM(score) DESC) a, (SELECT @rank := 0) b ;

16.1 查询学生的总成绩，并进行排名，总分重复时不保留名次空缺

解题思路：这一题，不熟练，参考了前辈们的做法

知识点：用户变量，子查询

SELECT a.*, CASE WHEN @fscore = a.sumscore THEN @rank WHEN @fscore := a.sumscore THEN @rank := @rank + 1 END AS 排名 FROM (SELECT sc.sid, SUM(score) AS sumscore FROM sc GROUP BY sid ORDER BY SUM(score) DESC) AS a, (SELECT @rank := 0, @fscore := NULL) AS t ;

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

解题思路：这个题和之前的那一题很像，都是使用case when 语句，然后加一个join

知识点：join case when，以及

SELECT sc.cid AS 课程编号, cname AS 课程名称, SUM( CASE WHEN score >= 0 AND score = 0 AND score = 60 AND score = 60 AND score = 70 AND score = 70 AND score = 85 AND score = 85 AND score = 85;

或者

SELECT sc.sid,sname,AVG(score) AS 平均成绩 FROM sc INNER JOIN student ON sc.sid = student.sid GROUP BY sc.sid HAVING 平均成绩>= 85;

结果都是第一样的，但是数据里大的时候我会使用第二种解法

27.查询课程名称为「数学」，且分数低于 60 的学生姓名和分数

解题思路：和上一题一样，可以使用join，也可以直接使用笛卡尔积，但是要使用三张表，所以最好还是使用join

知识点：多重join的使用

SELECT sname, score FROM course AS c INNER JOIN sc ON c.cid = sc.cid AND c.cname = '数学' AND sc.score 70 ;

或者

SELECT sname, sc.cid, score FROM sc INNER JOIN student AS s ON sc.sid = s.sid WHERE score > 70 ;

结果是一样的，至于两者的区别，可以参考下面这篇文章

https://blog.csdn.net/weixin_40539892/article/details/90263677

总结起来就是inner join后边时两个做法是没有区别的，但是如果是left join这种就会有多余的出来了，这是因为使用join以后会生成一个虚拟表，而使用where是在这张虚拟表的基础上进行筛选，所以结果可能不一样。这个值得注意。

30.查询不及格的课程

解题思路：这个其实和上面一题是同一个考法，不详细展开，唯一的区别就是只查询课程，所以就是要distinct，或者groupby

知识点：join，

SELECT sc.cid FROM sc INNER JOIN student AS s ON sc.sid = s.sid WHERE score 5 ;

38.检索至少选修两门课程的学生学号

解题思路：和上一题基本类似按照学生id分组然后count cid，筛选出大于等于2的学生

知识点：groupby having

SELECT sid, COUNT(cid) FROM sc GROUP BY sid HAVING COUNT(cid) >= 2 ;

39.查询选修了全部课程的学生信息

解题思路：同样的我们要知道总共有多少门课，不光是为了回答这一道题，可能真实情况会有很多的课，那么就需要把course表中所有的课程计数，还要求学生信息，那就需要使用join了

知识点：join，where，子查询

SELECT s.* FROM sc INNER JOIN student AS s ON sc.sid = s.sid WHERE cid = (SELECT COUNT(*) FROM course) ;

40.查询各学生的年龄，只按年份来算

解题思路：这题开始就要使用时间函数了，不难，还算比较简单

知识点：date函数的使用

SELECT sname, YEAR(NOW()) - YEAR(sage) AS 年纪 FROM student ;

41. 按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一

解题思路：这个可以使用case when语句，然后使用时间戳求差值

知识点：case when ，year，date_format

SELECT sname, CASE WHEN ( DATE_FORMAT(NOW(), '%m-%d') - DATE_FORMAT(sage, '%m-%d') ) < 0 THEN YEAR(NOW()) - YEAR(sage) + 1 ELSE YEAR(NOW()) - YEAR(sage) END AS 年龄 FROM student ;