【数据分析】- SQL必做常见面试50题（2/2）保姆级

30.暂无此题

31.查询1990年出生的学生名单（重点）

思路：查询某个日期区间內的资料的方法以及时间函数YEAR()的用法。

解题：

方法一：使用比较运算符 >,=,<,>=,<=

SELECT * FROM Student 
WHERE S_Birth<='1991-01-01' AND S_Birth>'1989-12-31'

方法二：BETWEEN... AND ...包括开头值又包括结尾值

会选择介于两个值之间的数据，这些值可以是数值、文本或者 日期 （要注意包含或者半包含）

SELECT * FROM Student 
WHERE S_Birth Between '1990-01-01' AND '1990-12-31'

方法三：模糊查询LIKE

SELECT * FROM Student 
WHERE S_Birth LIKE '1990%'

方法四：YEAR()

SELECT S_Id, S_Name, S_Birth FROM Student 
WHERE YEAR(S_Birth)='1990'

注意：

(1) 准备数据中，S_Birth的数据类型是DATE 格式是 YYYY-MM-DD， 在MySQL 中，其它日期格式还有：

• DATETIME - 格式是 YYYY-MM-DD HH:MI:SS
• TIMESTAMP - 格式是 YYYY-MM-DD HH:MI:SS
• YEAR 格式是 YYYY

(2) DQL语言里的单行函数之一——时间日期函数

• 数值函数 - ABS()返回绝对值、ROUND()四舍五入等
• 字符串函数 - CONCAT(s1,s2...sn)合并多个栏位、REPLACE(列名，旧值1，新值2)
• 时间日期函数 - 获取当前时间NOW()、当前时分秒CURTIME()、当前日期CURDATE()
• 流程控制函数 - IF(condition, expr1, expr2)、IFNULL(expr1, expr2)
• 其它函数 - 查看加密后的值（比如密码）SELECT MD5('aaa')

（3）时间日期函数

• 取现在的日期时间

 SELECT NOW();
 返回值：2022-03-12 23:24:20

• 取出日期时间中日期的部分

SELECT DATE('1990-01-01 01:02:03');
返回值：1990-01-01

• 取出日期时间的年份

SELECT YEAR('1990-01-01 01:02:03');
返回值：1990

• 取得现在的日期 CURDATE()

SELECT MONTH(CURDATE());
返回值：3 -- 当前是3月份

• 日期相减 DATEDIFF (expr1, expr2) 返回 expr1 − expr2，即两个日期相差几天，只有日期的部分会被相减，时间的部分会被忽略。

SELECT DATEDIFF('2022-03-12 23:59:59', '1990-05-20')
返回值：11619

• 给日期时间增加指定间隔 DATE_ADD ，给日期时间减去指定间隔 DATE_SUB

DATE_ADD(datetime, INTERVAL expr unit)
expr 用來指定你要加上的时间间隔，unit 是单位（可以是下列的值 SECOND/HOUR/DAY/WEEK/MONTH/YEAR）
例如：取一天后的时间：SELECT DATE_ADD('2022-03-12',INTERVAL 1 DAY); 返回值：2022-03-13

• 日期和时间相减 TIMESTAMPDIFF()

TIMESTAMPDIFF(unit, datetime_expr1, datetime_expr2)
SELECT TIMESTAMPDIFF(MINUTE, '2003-02-01', '2003-05-01 12:05:55');返回值：128885（相差几分钟）

32.查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩

思路： 先将用到的字段的表格Score（学号、平均成绩）和Student（姓名）先联结，然后按条件筛选。

解题：联结两表的效果如下图，

SELECT a.*, b.*  FROM Score AS a 
INNER  JOIN Student AS b 
ON a.S_Id=b.S_Id

上图需要GROUP BY 按照学生S_Id进行分组，分组后再筛选，而且筛选条件是聚合函数AVG，所以用HAVING。

SELECT a.S_Id, b.S_Name, AVG(S_Score)  FROM Score AS a 
INNER JOIN Student AS b 
ON a.S_Id=b.S_Id
GROUP BY a.S_Id  HAVING AVG(S_Score)>=85

注意： 另外一种方式会比较复杂，用子查询先将满足条件的S_Id取出来，再放进原始联结表里Score和Student。

SELECT b.S_Id, b.S_Name FROM Score AS a INNER JOIN Student AS b ON a.S_Id=b.S_Id
WHERE a.S_Id IN (SELECT S_Id FROM Score GROUP BY S_Id HAVING AVG(S_Score)>=85)

会出现三条结果，是因为这里的一个S_Id是有多条S_Score对应的。然后GROUP BY后，增加一条筛选AVG完事。相比较而言，用方法一比较省事。

SELECT b.S_Id, b.S_Name, AVG(S_Score) FROM Score AS a INNER JOIN Student AS b ON a.S_Id=b.S_Id
WHERE a.S_Id IN (SELECT S_Id FROM Score GROUP BY S_Id HAVING AVG(S_Score)>=85)
GROUP BY b.S_Id 

33.查询每门课程的平均成绩，结果按平均成绩升序排序，平均成绩相同时，按课程号降序排序

思路： 主要考察ORDER BY的 组合排序 用法：先按第一个字段进行排序，如果第一个字段相同，才按第二个字段进行排序，依次类推。

SELECT 字段名 FROM 表名 [WHERE 条件] ORDER BY 字段名1 [ASC|DESC],  字段名2 [ASC|DESC]......;

解题： 若语句中没有加上ASC 或 DESC，默认为ASC是升序排列。

SELECT C_Id, AVG(S_Score) FROM SCORE
GROUP BY C_Id 
ORDER BY AVG(S_Score), C_Id DESC

注意： “同一字段相同，才按照第二个字段进行排序“，会想到窗口函数 PARTITION BY分区后，同一分区字段里（相同字段）按照 ORDER BY 进行排序（第二字段排序）。不过关于分区的规则是什么，不是特别清楚（如果是字符按照英文字母顺序？如果是数字默认升序？）下面代码也能得到同样的结果，关于PARTITION BY分区的顺序规则，有知道的同学可以给我留言，3Q～

SELECT C_Id,AVG(S_Score)
,ROW_NUMBER()OVER(PARTITION BY AVG(S_Score) ORDER BY C_Id DESC)
FROM Score GROUP BY C_Id 

34.查询课程名称为“数学”，且分数低于60分的学生姓名和分数

思路： C_Name在Course表，S_Score在Score表，S_Name在Student表。可以用子查询，也可以用联结查询。并且联结查询可以部分联结或者3表联结。

解题：

法一：子查询

SELECT b.S_Name, a.S_Score FROM Score AS a INNER JOIN Student AS b ON a.S_Id=b.S_Id
WHERE b.S_Id IN 
(SELECT S_Id FROM Score WHERE C_Id=(SELECT C_Id FROM Course WHERE C_Name='数学') AND S_Score<60)

先用子查询，通过C_Name和S_Score定位，得到“数学分数低于60分的学生的 S_Id ”，再联结学生表获取学生姓名。

法二：部分联结

-- a.虚拟小表是个子查询

SELECT b.S_Name, a.S_Score FROM 
(SELECT S_Id, S_Score FROM Score 
WHERE C_Id=(SELECT C_Id FROM Course WHERE C_Name='数学') AND S_Score<60) AS a ---虚拟小表a
LEFT JOIN Student AS b ON a.S_Id=b.S_Id 

利用子查询将符合条件的S_Id, S_Score虚拟小表a取出。再和Student表左联结。

-- b.虚拟小表是联结查询

SELECT d.S_Name, c.S_Score  FROM 
(SELECT a.S_Id, a.S_Score FROM Score AS a 
INNER JOIN Course AS b ON a.C_Id=b.C_Id 
WHERE b.C_Name='数学' AND a.S_Score<60) AS c         --- 虚拟小表a
INNER JOIN Student AS d ON c.S_Id=d.S_Id

利用联结查询将符合条件的S_Id, S_Score虚拟小表a取出。再和Student表左联结。

法三：全部联结

SELECT c.S_Name, a.S_Score FROM Score AS a 
INNER JOIN Course AS b ON a.C_Id=b.C_Id
INNER JOIN Student AS c ON a.S_Id=c.S_Id
WHERE b.C_Name='数学' AND a.S_Score<60

所有表格先全部联结好，这里以Score表格为主表（Course和Student都是一对一的关系），然后再通过C_Name和S_Score等条件筛选。

35. 查询所有学生的课程及分数情况（重点）

思路： 其实，学生S_Id的课程C_Id和分数S_Score信息在Score表里都含有了。最多联结Student表格，可以看到学生姓名S_Name（ 还可以看到没有报课的学生的未报课的情况）。 联结Course可以看到课程名称C_Name。

SELECT a.S_Id, a.S_Name, c.C_Name, b.S_Score FROM Student AS a 
LEFT JOIN Score AS b ON a.S_Id=b.S_Id -- 防止有学生信息遗漏，用Student的Left联结
INNER JOIN Course AS c ON c.C_Id=b.C_Id

但这些都显然不可以成为 “重点” 题。原来大家是想看到这样一个效果：

解题： 先解决数学、语文、英语等课程的分数的显示问题，再考虑联结其它表格拿到S_Name等字段。

（1）首先 ，用CASE WHEN解决这种显示问题 （参见17题） ，在Score表格里面试下面这段，得到的结果如下图：

 SELECT S_Id
 ,(CASE WHEN C_Id=1 THEN S_Score ELSE NULL END) '语文'
 ,(CASE WHEN C_Id=2 THEN S_Score ELSE NULL END) '数学'
 ,(CASE WHEN C_Id=3 THEN S_Score ELSE NULL END) '英语'
 ,(CASE WHEN C_Id=5 THEN S_Score ELSE NULL END) '体能'
 ,(CASE WHEN C_Id=10 THEN S_Score ELSE NULL END) '美术'
 FROM Score

学号是1的同学考了3门，成绩显示3条，且无法按照学号GROUP BY S_Id分组，怎么才能让结果按照学号进行分组，成绩都在一行显示？

这里使用讨巧的方法，聚合函数 MAX (CASE WHEN)或者 SUM (CASE WHEN)。例如1号猴子同学，语文显示了3行（80/NULL/NULL）用MAX就是取这3个数里面的最大值（SUM求这里面3数之和），是唯一的。再聚合函数搭配分组GROUP BY即可完成。

SELECT S_Id
 ,SUM(CASE WHEN C_Id=1 THEN S_Score ELSE NULL END) '语文'
 ,SUM(CASE WHEN C_Id=2 THEN S_Score ELSE NULL END) '数学'
 ,SUM(CASE WHEN C_Id=3 THEN S_Score ELSE NULL END) '英语'
 ,SUM(CASE WHEN C_Id=5 THEN S_Score ELSE NULL END) '体能'
 ,SUM(CASE WHEN C_Id=10 THEN S_Score ELSE NULL END) '美术'
 FROM Score
 GROUP BY S_Id

（2）然后， 需要联结其它表格里的字段，比如Student里面的S_Name，这里用RIGHT JOIN Student表格可以防止没有报名考试的学生被遗漏。

SELECT b.S_Id, b.S_Name
 ,SUM(CASE WHEN C_Id=1 THEN S_Score ELSE NULL END) '语文'
 ,SUM(CASE WHEN C_Id=2 THEN S_Score ELSE NULL END) '数学'
 ,SUM(CASE WHEN C_Id=3 THEN S_Score ELSE NULL END) '英语'
 ,SUM(CASE WHEN C_Id=5 THEN S_Score ELSE NULL END) '体能'
 ,SUM(CASE WHEN C_Id=10 THEN S_Score ELSE NULL END) '美术'
 FROM Score AS a 
 RIGHT JOIN Student AS b ON b.S_Id=a.S_Id 
 GROUP BY b.S_Id

（3）最后 ，完善下CASE WHEN里面的C_Id，改成C_Name，这样就“不用自己判断C_Id=1是语文”。

 SELECT b.S_Id, b.S_Name
 ,SUM(CASE WHEN C_Name='语文' THEN S_Score ELSE NULL END) '语文'
 ,SUM(CASE WHEN C_Name='数学' THEN S_Score ELSE NULL END) '数学'
 ,SUM(CASE WHEN C_Name='英语' THEN S_Score ELSE NULL END) '英语'
 ,SUM(CASE WHEN C_Name='体能' THEN S_Score ELSE NULL END) '体能'
 ,SUM(CASE WHEN C_Name='美术' THEN S_Score ELSE NULL END) '美术'
 FROM Score AS a 
 INNER JOIN Course AS c ON c.C_Id=a.C_Id
 RIGHT JOIN Student AS b ON b.S_Id=a.S_Id 
 GROUP BY b.S_Id

注意：按照视频 ， 如果是选择先将表格联结好，再case when 的话，MYSQL8.0会报错。

此外如果学校里面设置了很多门课程，不只这里的5门课，该怎么操作？欢迎留言学习～

SELECT b.S_Id
 ,CASE WHEN c.C_Name='语文' THEN a.S_Score ELSE NULL END 
 FROM Score AS a 
 INNER JOIN Course AS c ON c.C_Id=a.C_Id
 RIGHT JOIN Student AS b ON b.S_Id=a.S_Id 
 GROUP BY b.S_Id
1055 - Expression #2 of SELECT list is not in GROUP BY clause and 
contains nonaggregated column 'student.c.C_Name' 
which is not functionally dependent on columns in GROUP BY clause; 
this is incompatible with sql_mode=only_full_group_by, Time: 0.002000s

36. 查询课程成绩在70分以上课程名称，分数和学生姓名

思路： 在Score表格里，查询课程成绩在70分以上的分数（默认一个学生学一门课考一个分数）。

SELECT S_Id, C_Id, S_Score FROM Score WHERE S_Score>70

如果题目不需要“课程名称“和“学生姓名”，只要有S_Id和C_Id，那一张Score表格就完事了。但是因为需要的C_Name和S_Name分别在Course和Student表里，且一个S_Id对应一个S_Name，一个C_Id对应一个C_Name，“一一对应”的关系用INNER JOIN。

解题：

SELECT c.S_Id, c.S_Name, b.C_Name, a.S_Score FROM Score AS a 
INNER JOIN Course AS b ON a.C_Id=b.C_Id
INNER JOIN Student AS c ON a.S_Id=c.S_Id
WHERE a.S_Score>70

37. 查询不及格的课程并按课程号从大到小排列

思路：（和36题类似） 在Score表格里，查询课程成绩在60分以下的分数，对应的学生S_Id和C_Id，按照C_Id降序排列。考虑到看结果的人的观感，再考虑适当联结其它表格，取让人看得比较明白的字段。

解题： 仅按照题目的结果如下：

SELECT *  FROM Score 
WHERE S_Score <60
ORDER BY C_Id DESC

考虑观感，优化如下：

SELECT a.C_Id, b.C_Name, c.S_Id, c.S_Name, a.S_Score FROM Score AS a 
INNER JOIN Course AS b ON a.C_Id=b.C_Id
INNER JOIN Student AS c ON a.S_Id=c.S_Id
WHERE a.S_Score<60
ORDER BY a.C_Id DESC

38. 查询课程编号为03且课程成绩在80分以上的学生的学号和姓名

思路： 复习题，从Score表里找出符合条件的学生的学号，再与Student表格联结，查处姓名。为了观感，可以多选出C_Id和S_Score等字段。方法二，用子查询的方法也很简单。

解题：

SELECT sc.S_Id, st.S_Name FROM Score AS sc
INNER JOIN Student AS st ON sc.S_Id=st.S_Id
WHERE sc.C_Id=3 AND sc.S_Score>80

注意： 此题还可以用子查询的方式

SELECT S_Id, S_Name  FROM Student 
WHERE S_Id IN(
SELECT S_Id FROM Score WHERE C_Id=3 AND S_Score>80)

39. 求每门课程的学生人数

思路： 属于复习题，需要考虑到可能有些课程无人选的情况。所以以course表里检索的课程来分组，统计每门课程在score表里学生选的人数。

解题：

SELECT c.C_Id, COUNT(DISTINCT S_Id) FROM Score AS sc
RIGHT JOIN Course AS c ON sc.C_Id=c.C_Id
GROUP BY c.C_Id

注意： 右联结course表格，视频中INNER JOIN 不够严谨。

40. 查询选修“孟扎扎”老师所授课程的学生中成绩最高的学生姓名及其成绩

思路： 一名老师 T_Name 对应一个 T_Id（Teacher表），他可能教多门课程 C_Id（Course表） 。一个 C_Id 对应一个最高分MAX(S_Score）(Score表），注意这个最高分可能是多个考生 S_Id 并列的情况。最后一个考生 S_Id 对应一个学生姓名 S_Name（Student表）。

下面提供两种写法，一种是子查询与联结查询结合，另外一种是全部联结查询。然后再与实际业务问题去匹配检验。

解题：

方法一（老师只教一门课）

SELECT st.S_Name, st.S_Id, sc.S_Score FROM Score AS sc  
INNER  JOIN Student AS st ON sc.S_Id=st.S_Id WHERE S_Score=(     -- 学生分数是最高成绩
SELECT MAX(S_Score) FROM Score                                   -- 最高的成绩 
WHERE  C_Id=(SELECT C_Id FROM Course WHERE T_Id=(
SELECT T_Id FROM Teacher WHERE T_Name='孟扎扎') )                 -- 老师所授课程 
GROUP BY  C_Id)

利用子查询的步骤：首先，找到“T_Name孟扎扎的老师，所教课程的课程C_Id是什么（Teacher表）“，接着用得到的C_Id在Score表里面查出“课程最高分Max(S_Score)”，再找到“哪些学生”考了这些分数，最后在（Student表）拿出学生姓名（这里只做一个简单的一一对应联结 / 同34题方法一） 。

这个结果只符合咱们这个题目“孟扎扎老师只教了一门课”。如果孟扎扎老师教授多门课程的情况就不适用了。

（老师教多门课）

SELECT a.C_Id, a.最高成绩, st.S_Name, st.S_Id  FROM (
SELECT C_Id, MAX(S_Score) AS '最高成绩' FROM Score 
WHERE C_Id IN (SELECT C_Id FROM Course WHERE T_Id=(SELECT T_Id FROM Teacher WHERE T_Name='孟扎扎') ) 
GROUP BY C_Id)  AS a                                         ---a 孟扎扎老师教的各门课程以及最高成绩
INNER JOIN Score AS sc ON a.最高成绩=sc.S_Score AND a.C_Id=sc.C_Id
INNER JOIN Student AS st ON sc.S_Id=st.S_Id
GROUP BY a.C_Id, a.最高成绩, st.S_Name, st.S_Id

此时，先看中间部分的子查询a，“在Score表里，查出课程C_Id是(IN)孟扎扎老师教的课程编号以及对应的课程最高分“。为了测试，我将原Course表格里面的“10美术课“也改成孟扎扎(T_Id)=1，在Score表里面把S_Id是5，报名C_Id是“10美术课”的学生分数改为90。将取到的结果命名为a，如下表所示：

再将这个虚拟的a表，去内联结Score 表格，注意这里的联结条件可以是两个，如果只用一个联结会出错（AND） a.最高成绩=sc.S_Score AND a.C_Id=sc.C_Id ；再内联结Student表，最后取出四个数据项（ a.C_Id, a.最高成绩, st.S_Name, st.S_Id）得到结果如下图：

方法二（全联结）

SELECT sc.C_Id, sc.S_Score, sc.S_Id , st.S_Name FROM Score AS sc
INNER JOIN Course AS c ON c.C_Id=sc.C_Id 
INNER JOIN Teacher AS t ON t.T_Id=c.T_Id 
INNER JOIN Student AS st ON sc.S_Id=st.S_Id 
WHERE t.T_Name='孟扎扎' 	                               
GROUP BY sc.C_Id, sc.S_Score, sc.S_Id
ORDER BY S_Score DESC 
LIMIT 0，5

首先，Score表作为主要的表，去内联结所有用到的表格。因为Score表格里面有S_Score / C_Id / S_Id三个重要字段，如果只有这个表格，咱们就可以做到“选出每门课程最高分的学生”。剩下的三张表格，一一对应，内联结上Course表（INNER：一个课程可以由多个老师教，一个老师可以教多门课）和Teacher表，再一一对应联结好Student表，然后取出字段 sc.C_Id, sc.S_Score, sc.S_Id , st.S_Name。

再做一个降序排列Order by S_Score DESC ，再用Limit取出最高分数的几名，如下图：

注意： 这里按照先排序再Limit会出现几个问题，欢迎补充讨论～

1. Limit 究竟写几？ 如果按照视频，Limit 0，1 取了第一名，是符合本表的一个老师教一门课，且这门课只有一个学生是最高分的情况。那先不说这个老师教几门课，比如在全国范围内，分数并列的有成千上万，Limit取多少呢？
2. 如果这个老师教了两门课C_Id=2和C_Id=10，这种就更尴尬了，LIMIT取3，看不到另外一门课的最高分。如果取4，也是咱们 看到最后的结果 ，然后 为了展示效果，排序取 出来的4行，LIMIT 4。

此外，关于 LIMIT的用法 ，可以参考上图：Limit 0，4 与Limit 4。

41. 查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩（重点）

思路： 刚拿到这题，感觉会出现多种理解。再梳理梳理题干，题目可能想表达的意思，我居然能拎出4种。在 不考虑实际业务场景 的情况下，单就练习不同角度的取数方法，选择其中较为清晰的（1，2，3）三条进行解答。

解题：

（1）【视角学生】 查询学生不同课程的成绩里， 分数完全相同 的学生信息；

备注 ：学生考三门，三门都得是一个分数；考三门，只有两门是一个分数，其它一门不是，都不选。

如下图：取张三，不取李四：

方法一：

SELECT c.S_Id, c.S_Score, c.C_Id  FROM 
(SELECT S_Id, S_Score, COUNT(C_Id) 同分课程数 FROM Score        
GROUP BY S_Id, S_Score HAVING COUNT(C_Id)>1) AS a       --  a.每个同学，相同分数的课程数
INNER JOIN 
(SELECT S_Id, COUNT(C_Id) 课程总数 FROM Score 
GROUP BY S_Id) AS b                                     --  b.每个学生，报名的课程总数
INNER JOIN Score AS c ON b.S_Id=c.S_Id
ON a.同分课程数 = b.课程总数 AND a.S_Id = b.S_Id

（a表 ）先将 “每个同学有相同分数的课程” 进行合并，如果这个课程数和（ b表）“每个学生报名的课程总数” 一致的话，就说明这个同学所有科目的分数都是一样的。选择用INNER JOIN（一一对应的关系），两表用“学生编号”与“课程数”进行联结。

方法二：

这是视频中的解答，求出了S_Id，后面信息要取的话，可以参照方法一

SELECT a.S_Id FROM 
(SELECT S_Id, S_Score FROM Score GROUP BY S_Id, S_Score ) AS a      -- a.将每个同学相同分数的课程合并起来
INNER JOIN 
(SELECT S_Id FROM Score GROUP BY S_Id HAVING COUNT(S_Id)>1) AS b    -- b.只报一门课的学生S_Id
ON a.S_Id=b.S_Id
GROUP BY a.S_Id HAVING COUNT(a.S_Score)=1

（a表） 先将每个同学相同分数的课程折叠起来，这样满足全部课程分数相同的同学，应该显示出来是一个S_Id搭一条S_Score的结果。其它非全部课程分数相同的学生，会显示出S_Id搭多条分数的结果：

同时注意到学生S_Id=3满足条件，此时，还应该从这里面排除“这名学生只报一门课“的情况 (b表） ，两表联结即可。

（2）【视角学生】 查询学生不同课程的成绩里， 部分分数相同 的信息（不同的分数不显示）；

备注 ：与（1）的区别，不要求学生的所有门课的分数完全相同，即考三门，只要两门相同或者三门相同都可以被选上。如上图：张三考了三门课，三门分数都一个数90，李四也考了三门课，只有两门分数是90，另外一门外语是50。张三和李四都可以被选上，只是展示的时候，外语的50分就不要了。

如果是（1）的情况，李四选不上。效果如下图：

SELECT a.S_Id, a.S_Score, sc.C_Id FROM 
(SELECT S_Id, S_Score, COUNT(C_Id) FROM Score
GROUP BY S_Id, S_Score HAVING COUNT(C_Id)>1)AS a    a.考多门课的学生里，相同分数的情况折叠
LEFT JOIN Score AS sc                               sc.再将折叠的学生、分数和课程展开
ON a.S_Id=sc.S_Id AND a.S_Score=sc.S_Score

先将“报多门课程的同一学生，考相同分数的情况“展示出来，已经筛选掉了只考一门课的学生了。再做链接一次Score表格，将所有折叠信息展开。

（3）【视角是课程】 查询不同课程里，成绩并列的学生信息；

备注 ：每门课出现并列的情况信息，仅展示并列的部分。如下图语文 ：取（张三+李四）、（赵六+钱七+孙八）

方法一：

这种情况的排列，最先想到的是，它特别像 窗口函数 的效果，“同一门课程里，分数由高到低排列“，然后从这里面 刨掉非并列， 即一门课只有一个分数的情况。

SELECT a.C_Id, a.S_Score, a.S_Id FROM 
(SELECT C_Id, S_Score, S_Id,
RANK()OVER(PARTITION BY C_Id ORDER BY S_Score DESC) AS 排名
FROM Score) AS a                                         -- a：按照C_Id分组，分数降序排列
INNER JOIN
 (SELECT C_Id, COUNT(S_Score), S_Score FROM  Score 
GROUP BY C_Id, S_Score HAVING COUNT(S_Score)>1) AS b     -- b:刨掉一门课程里只有一个分数的C_Id
ON a.C_Id=b.C_Id AND a.S_Score=b.S_Score  

窗口函数，看课程2，并列第一的两个分数取出来，之后刨掉剩下的仅有一个分数的80/60的情况。

方法二：

因为增加的rank()列其实和S_Score列有些重复，所以简化版本如下，直接 (a表) 求出一门课有多个分数的课和分数，再通过两个字段（C_Id和S_Score）联结S_Score表格，展开结果即可。

SELECT a.C_Id, a.S_Score, sc.S_Id FROM 
(SELECT C_Id, S_Score, COUNT(S_Score) FROM  Score 
GROUP BY  C_Id, S_Score HAVING COUNT(S_Score)>1) AS a     --a:一门课程里有多个分数的C_Id
LEFT JOIN Score as sc 
ON a.C_Id=sc.C_Id AND a.S_Score=sc.S_Score       --b:再通过和Score联结，展开这些C_Id即可

（4）【视角分数】 成绩一样的不同课程的学生情况；

只是这种情况，取前三条（张三、李四、王五）展示，不过张三和李四课程相同都是语文；只展示张三的两条成绩，就符合（1或者2），取四条（张三、李四、王五、张三）展示吗？

观察前三种解答，固定的量要么是（学生+分数）=1 and 2，要么是（课程+学生)=3。咱这4的情况，是只考虑一个固定下来的量（分数），其它两个量（课程）（学生）都在变化。所以笔者放弃了，如果有小伙伴扩展的思路的话， 欢迎留言 ～

42. 查询每门功成绩最好的前两名（同22和25题）

43. 统计每门课程的学生选修人数（超过5人的课程才统计）要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列

思路： 直接在Score表里面操作，后面这些题，不用联结查询感觉轻松多了：）因为原表里面没有符合条件的数据，我在Navicat里面增加了几条，验证完回滚就好了。

解题： 选修人数用的聚合函数，先GROUP BY后再HAVING，最后按照要求排序。

  SELECT C_Id AS 课程, COUNT(Distinct S_Id) AS 选修人数 FROM Score
	 GROUP BY C_Id  HAVING COUNT(Distinct S_Id)>5
	 ORDER BY COUNT(Distinct S_Id) DESC, C_Id

注意： 这里用Distinct防止在Score表格里面，有一些补考、重考的考生被算多次。其次，这题不能像33题那样用窗口函数，因为这里分区COUNT()，要求的是降序，不是自然升序，所以 Patition by 选修人数就不行了。

关于Navicat的回滚好像比较麻烦，搜了一下，可以先备份好，再改数据。操作链接如下： https:// jingyan.baidu.com/artic le/7e4409530b72d52fc0e2efe4.html

或者先选中表格， 右键--复制--结构+数据， 先存一份也可以。

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

思路： 至少选修两门，不用考虑没选课的同学。类似上题，GROUP BY S_Id，去重统计选修课程数即可。

解题：

SELECT S_Id, COUNT(DISTINCT C_Id) AS 选课数 FROM Score 
GROUP BY S_Id HAVING COUNT(DISTINCT C_Id)>=2

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

思路： 在course表格里用子查询先查出全部课程的数目COUNT(C_Id) FROM Course，然后Score表里查出每个学生选修的课程数COUNT(DISTINCT sc.C_Id)，二者相等，最后，要去Student表格里选学生的信息，用联结查询。

解题：

SELECT st.*, COUNT(DISTINCT sc.C_Id) AS 选修课数 FROM Score AS sc 
INNER JOIN Student AS st ON sc.S_Id=st.S_Id
GROUP BY sc.S_Id HAVING COUNT(DISTINCT sc.C_Id)=(SELECT COUNT(C_Id) FROM Course)

注意： 题目没说学生哪些信息，就全取了。但是Select * 要慎用。

46.查询各学生年龄

思路： Student只有出生年月S_Birth，所以 当前年龄 = 当前日期CURDATE() - 出生日期S_Birth ，用DATEDIFF。再整体除以365天（没考虑闰年情况），会获得带小数点的结果，最后向下取整算年龄 FlOOR() 。

解题：

SELECT S_Id, S_Name, FLOOR(DATEDIFF(CURDATE(),S_Birth)/365) FROM Student

注意：

（1） DATEDIFF(d1,d2) 计算日期 d1->d2 之间相隔的天数，d1是现在的时间，别颠倒。

（2） 为什么用 向下取整的FLOOR() 不用 四舍五入的ROUND()？ 举个例子，绝对年龄计算出是23.3和23.8，用ROUND 那么就是23和24岁。投射到现实生活中，你和你哥们两都是过了23岁生日，24岁生日还差几月，那你和你哥们能四舍五入，一个是23岁，另外一个24岁吗？你两都是23岁，向下取整用FLOOR。

（3） 另外弹幕有用 CEIL， 查了一下不合适

（4） 如果用YEAR()函数“year(curdate()) - year(s_birth)”这样会把年龄算大，要看过了生日没有。举个例子：现在curdate()是2022年4月6日，你的出生日期是1989年5月6日

SELECT year(curdate())      --- 返回2022
SELECT year('1989/05/06')   --- 返回1989
相减--33
SELECT FLOOR(DATEDIFF(CURDATE(),'1989/05/06')/365) --- 返回32

第一种year()把你算大了，为什么？因为你还没有过生日，只有你过了2022年5月6日生日，SELECT year(curdate())才能直接返回2022，否则就应该是2021。 用（年1）-（年2），需满足（日期1）减去（日期2）>0 ，否则结果就是（年1）- 1年 -（年2）

47. 查询没学过“张三”老师讲授的任一门课程的学生姓名（同5、13题）

48.查询下周过生日的同学

思路： 这里考察的是MYSQL中 时间转换周 的WEEK(date,[mode])函数。查看 给定日期属于哪个周数 使用。

WEEK函数接受两个参数：date是要获取周数的日期。mode是一个可选参数，用于确定周数计算的逻辑，它允许指定本周是从星期一还是星期日开始，返回的周数范围在0到52之间或0到53之间。如果忽略mode参数，默认情况下WEEK函数mode为0，即 一周的第一天是从周日 开始算。

首先，MYSQL中，默认是从【周日至周一】周日是一周的第一天，这符合国外的习惯，而中国是【周一至周日】算一周，周一是一周的第一天，这里mode就为1。

其次，一年的正常年份为365天，闰年为366天。一年又可以分为许多周，每周有7天。 所以一年，我们经常有365/7 = 52周，所以周范围在0-52或者0-53之间。

上面是WEEK函数的用法，再回到这题“查询下周过生日”，已经知道 当前的年月 Curdate()2022.04.08，使用WEEK函数，可以得到“当前时间属于今年的哪个周”。那么 “下周“ ，就是在WEEK(Curdate())的基础上+1，即“下周属于今年的哪个周“，要找在这个期间过生日的同学。此外，我们还知道 学生的出生年月S_Birth ，使用WEEK函数，可以得到“学生出生时间是属于当年的哪个周”。所以如果满足

WEEK(S_Birth)=WEEK(Curdate())+1   -- CURDATE()返回当前日期

是不是就是下周过生日的同学呢？这样我们只要下面的语句就可以完成了。

SELECT * FROM Student WHERE WEEK(S_Birth)=WEEK(Curdate())+1

这里是有个前提的（需要结合定义看）即“过生日的年“和”今年“的初始周数一致，周数计算都是一致的。那存不存在“不同年份周数初始周数不一致”的情况呢？看最近几年的新年第一天：

SELECT WEEK('2017/1/1',0)    返回值1
SELECT WEEK('2018/1/1',0)  返回值0
SELECT WEEK('2019/1/1',0)  返回值0
SELECT WEEK('2020/1/1',0)  返回值0
SELECT WEEK('2021/1/1',0)  返回值0
SELECT WEEK('2022/1/1',0)  返回值0

从结果看，存在“ 不同年份，初始周数不一致，计算周数不一致的情况“。 比如在2017年周数4出生的人，在2022年周数到4的时候，生日都过完了。怎么才能做到统一？

要保证周数计算在一个水平上，而且生日只看月日，不看年份，我们就可以把出生日期的年份改变成今年，既保证周数计算规则一致，也不影响结果。这里使用substring()“用于截取字符串的某部分”和concat()“用于连接两个或多个数组”。

解题：

substring基本语法： substring(字符串或者列名,起始位置,截取长度) ，substring('1990-01-20',6,5)从第六个字符开始，截取5位。

SELECT substring('1990-01-20',6,5)   返回日期  01-20

concat基本语法：concat(字符串1，字符串2...) CONCAT('2022-', substring(S_Birth,6,5))，将出生日期嫁接到今年。

SELECT CONCAT('2022-', substring(S_Birth,6,5)) FROM Student   

最终的结果：

SELECT * FROM Student WHERE WEEK(CONCAT('2022-',substring(S_Birth,6,5)))=WEEK(CURDATE()) 
默认mode取0

注意：

一、MODE

为什么会出现“ 不同年份，初始周数不一致，计算周数不一致“ 的情况？主要是WEEK(date,[mode])函数，MODE不同，即使MODE相同，不同年份初始周也不一样。下面我们就来详细说说～

MODE双参数的形式WEEK()函数，允许你 指定一周的第一天 从周日或周一开始， 以及 选择 返回值范围 在0到53或1到53之间。

MODE 的理解难点（对于刚接触的人）

（1）在于计算机的语言和人类正常判断有偏差 。

咱们会自然认为元旦在的那个周就是一年的第一周，但其实元旦在这里可能是第0周，也可能是上年的5*周。比如，在MODE为3的情况下：

SELECT WEEK('2021/1/1',3)  # 运行结果：53
SELECT WEEK('2021/1/1',0)  # 运行结果： 0
SELECT WEEK('2017/1/1',0)  # 运行结果： 1

（2）MODE 表格的使用方法问题。

首先需要跟计算机定一个规矩，指定我们想要的 年度一周的开始 是周一还是周日。然后，再从对应表格里面的返回值范围，指定是0开始，还是1开始。根据这两就可以判断，本年度在第一周开始前的那一部分日期，是算作第0周，还是上年的第5x周（包括元旦1月1号开始的情况）找几个例子试试，基本不会出错。

SELECT WEEK('2022/1/1', 1 ) # 运行结果：0 
SELECT WEEK('2022/1/1', 3 ) # 运行结果：52

当模式为1的时候，对照模式表可以得知其计算逻辑是：把星期一当作一周的开始，那么2022年1月1日至第一个周一（2022年1月3日）只有2天（1月1日、1月2日），不足3天，那么2022年1月1日、1月2日计为 第0周 ，1月3日开始是第1周。

当模式为3的时候，对照模式表可以得知其计算逻辑是：把星期一当作一周的开始，那么2022年1月1日至第一个周一（2022年1月3日）只有2天（1月1日、1月2日），不足3天，那么2022年1月1日、1月2日计为 上一年度的第52周 ，1月3日开始是第1周。

二、MYSQL中时间转换周的另外两个函数（WEEKOFYEAR / DATE_FORMAT（DATE，’%U’））

（1）WEEKOFYEAR(date) 返回日期用数字表示的范围是从1到53的日历周。WEEKOFYEAR()是一个兼容性函数，它等效于 WEEK(date,3) 。

（2）DATE_FORMAT（date,’%U’） 跨年周， 上一年的日期为去年最后一周，本年日期为本年第一周 。这种比较符合咱们的理解，就不会出现明明是新年元旦还被计到上周52周。

49.查询本月过生日的人

思路： 已知现在日期CURDATE()，出生日期，使用MONTH函数，MONTH(当前日期)=MOTH(出生日期)即可。

解题： SELECT * FROM Student WHERE MONTH(S_Birth)=MONTH(CURDATE())

备注：其它几种案例

（1）查询7天内生日的学生

思路： 在mysql中，DATE_FORMAT(date, format) 函数用于以不同的格式显示日期/时间数据。date 参数是合法的日期。format 规定日期/时间的输出格式。可以有的格式如下：

DATE_FORMAT(NOW(),'%m-%d-%Y')    返回值：11-04-2022

出生日期7天内，当前日期加上6天（当天在内），这个范围都叫7天内，用DATE_ADD函数。

SELECT * FROM Student
WHERE DATE_FORMAT(S_Birth,'%m-%d')>=DATE_FORMAT(NOW(),'%m-%d')
AND  DATE_FORMAT(S_Birth,'%m-%d')<=DATE_FORMAT(DATE_ADD(NOW(),INTERVAL 6 DAY),'%m-%d')

（2）查询生日是今天的用户

DATE_FORMAT()或者MONTH() 与DAY()的合用。

SELECT * FROM Student