Mysql经典45道题(详解31-45)

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

解题思路:

分析题目关键词:“课程编号”、“成绩”、“学生学号”、“学生姓名”

第一步:锁定表，成绩表、学生表

第二步:关联查询

SELECT a.*,b.Sname
FROM sc AS a
LEFT JOIN student AS b
ON a.SId = b.SId
WHERE a.CId = '01' AND a.score >=80;

展示结果:

扩展

-- 第二种解法:
SELECT b.*,a.Sname
FROM student AS a 
INNER JOIN (SELECT *
            FROM sc
            WHERE CId = '01' AND score >= 80) AS b
ON a.SId = b.SId;

-- 第三种解法:
SELECT b.*, a.Sname 
FROM student AS a 
INNER JOIN sc AS b 
ON a.Sid = b.Sid 
WHERE b.Cid = 01 
AND b.score >= 80;

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

解题思路:

分析题目关键词:“每门课程”、“人数”

第一步:锁定表，成绩表

第二步:分组聚合

SELECT CId,COUNT(*) AS ct
FROM sc
GROUP BY CId;

展示结果:

33.假设成绩不重复，查询选修了[张三]老师所授课的学生中，成绩最高的学生信息及其成绩* 

解题思路:

分析题目的关键词:“张三老师”、“成绩不重复”、“成绩最高”、“学生信息”、“成绩”

第一步:锁定表，教师表、课程表、成绩表、学生表

第二步:关联4张表

SELECT *
FROM sc AS a
LEFT JOIN student AS b
ON a.SId = b.SId
LEFT JOIN course AS c
ON a.CId = c.CId
LEFT JOIN teacher AS d
ON c.TId = d.TId;

第三步:增加筛选条件,[张三]老师 

SELECT *
FROM sc AS a
LEFT JOIN student AS b
ON a.SId = b.SId
LEFT JOIN course AS c
ON a.CId = c.CId
LEFT JOIN teacher AS d
ON c.TId = d.TId
WHERE d.Tname = '张三';

第四步:按照分数排序，只显示第一条记录(limit)

SELECT b.*,score,Tname
FROM sc AS a
LEFT JOIN student AS b
ON a.SId = b.SId
LEFT JOIN course AS c
ON a.CId = c.CId
LEFT JOIN teacher AS d
ON c.TId = d.TId
WHERE d.Tname = '张三'
ORDER BY score DESC
LIMIT 1;

展示结果:

扩展:INNER JOIN也可以 

34.假设成绩有重复的情况下，查询选修[张三]老师所授课的学生中，成绩最高的学生信息及其成绩*

解题思路:

分析题目的关键词:“张三老师”、“成绩重复”、“成绩最高”、“学生信息”、“成绩”

第一步:锁定表，教师表、课程表、成绩表、学生表

第二步:关联4张表，并筛选出选修[张三]老师所授课程的记录

SELECT *
FROM sc AS a
LEFT JOIN student AS b
ON a.SId = b.SId
LEFT JOIN course AS c
ON a.CId = c.CId
LEFT JOIN teacher AS d
ON c.TId = d.TId
WHERE d.Tname = '张三';

第三步:使用dense_rank()进行排名

SELECT 
b.*，a.score,d.Tname,
dense_rank() over (ORDER BY score DESC) AS score_rank
FROM sc AS a
LEFT JOIN student AS b
ON a.SId = b.SId
LEFT JOIN course AS c
ON a.CId = c.CId
LEFT JOIN teacher AS d
ON c.TId = d.TId
WHERE d.Tname = '张三';

第四步:筛选出第1名

SELECT *
FROM(SELECT 
     b.*，a.score,d.Tname,
     dense_rank() over (ORDER BY score DESC) AS score_rank
     FROM sc AS a
     LEFT JOIN student AS b
     ON a.SId = b.SId
     LEFT JOIN course AS c
     ON a.CId = c.CId
     LEFT JOIN teacher AS d
     ON c.TId = d.TId
     WHERE d.Tname = '张三') 
WHERE score_rank = 1;

35.查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩* 

解题思路:

分析题目的关键词:“不同课程”、“成绩相同”

第一步:锁定表，成绩表

第二步:自连接成绩表，连接条件是学生编号相同、课程编号不同、成绩相同

SELECT a.*
FROM sc AS a
INNER JOIN sc AS b
ON a.SId = b.SId AND a.CId != b.CId AND a.score = b.score;

第三步:去重

SELECT DISTINCT a.*
FROM sc AS a
INNER JOIN sc AS b
ON a.SId = b.SId AND a.CId != b.CId AND a.score = b.score;

展示结果:

36.查询每门科目成绩最好的前两名* 

解题思路:

分析题目的关键词:“每门科目”、“成绩最好的前两名”

第一步:锁定表，成绩表

第二步:使用dense_rank()排名

SELECT
*,
dense_rank() over (PARTITION BY CId ORDER BY score DESC) AS score_rank
FROM sc;

第三步:筛选出前两名 

SELECT *
FROM (SELECT
      *,
      dense_rank() over (PARTITION BY CId ORDER BY score DESC) AS score_rank
      FROM sc) AS a
WHERE score_rank <= 2;

 37.统计每门课程的学生选修人数(超过5人的课程才统计)

解题思路:

分析题目的关键词:“每门课程”、“选修人数”、“超过5人才统计”

第一步:锁定表，成绩表

第二步:分组聚合，求每门课程的选修人数

SELECT CId,COUNT(*) AS ct
FROM sc
GROUP BY CId;

 

第三步:筛选出选修人数大于5的记录

SELECT CId,COUNT(*) AS ct
FROM sc
GROUP BY CId
HAVING ct > 5;

展示结果:

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

解题思路:

分析题目的关键词:“选修两门”、“学生学号”

第一步:锁定表，成绩表

第二步:分组聚合，求出每名学生的选修课程数

SELECT SId,COUNT(*) AS ct
FROM sc
GROUP BY SId;

第三步:筛选出选修课程数大于等于2的学生

SELECT SId,COUNT(*) AS ct
FROM sc
GROUP BY SId
HAVING ct >= 2;

展示结果:

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

 解题思路:

分析题目的关键词:“选修全部课程”、“学生信息”

第一步:锁定表，学生表、成绩表、课程表

第二步:求出课程表的总课程数

SELECT COUNT(*) FROM course;

第三步:求出每名学生的选修课程数

SELECT SId,COUNT(*)
FROM sc
GROUP BY SId;

 第四步:筛选出学习全部课程的学生学生SId

SELECT SId,COUNT(*)
FROM sc
GROUP BY SId
HAVING COUNT(*) = (SELECT COUNT(*) FROM course);

第五步:使用子查询，获取学生信息

SELECT *
FROM student
WHERE SId IN(SELECT SId
             FROM sc
             GROUP BY SId
             HAVING COUNT(*) = (SELECT COUNT(*) FROM course));

展示结果:

扩展

SELECT a.* 
FROM student AS a 
INNER JOIN score AS b 
ON a.Sid = b.Sid 
GROUP BY a.Sid 
HAVING COUNT(b.Cid) = (SELECT COUNT(*) FROM course);

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

 解题思路:

分析题目关键词:“年龄”、“按年份来算”

第一步:锁定表，学生表

SELECT * FROM student;

 第二步:使用NOW()、YEAR()函数计算年龄

SELECT *,(YEAR(NOW()) - YEAR(Sage)) AS age
FROM student;

展示结果:

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

 解题思路:

分析题目的关键词:“年龄”、“当前月日<出生年月的月日，则年龄减一”

第一步:锁定表，学生表

第二步:使用NOW()、TIMESTAMPDIFF()函数计算年龄

SELECT *,TIMESTAMPDIFF(YEAR,Sage,NOW()) AS age
FROM student;

展示结果:

扩展

42.查询本周过生日的学生 

解题思路:

分析题目:“周数”

第一步:锁定表，学生表

第二步:使用WEEK()函数查询

SELECT *
FROM student
WHERE WEEK(Sage) = WEEK(NOW());

43.查询下周过生日的学生 

解题思路:

分析题目:“周数”

第一步:锁定表，学生表

第二步:使用WEEK()函数查询

SELECT *
FROM student
WHERE WEEK(Sage) = WEEK(NOW())+1;

 44.查询本月过生日的学生

解题思路:

分析题目:“月份”

第一步:锁定表，学生表

第二步:使用MONTH()函数查询

SELECT *
FROM student
WHERE MONTH(Sage) = MONTH(NOW());

 45.查询下月过生日的学生

解题思路: 

分析题目:“月份”

第一步:锁定表，学生表

第二步:使用MONTH()函数查询

SELECT *
FROM student
WHERE MONTH(Sage) = MONTH(NOW())+1;

展示结果:

(完成此题当前月份为11月份，下月份为12月份)