Mysql经典45道题(详解16-30)

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

解题思路:

分析题目:“总分”、“保留名次空缺”

第一步:计算总成绩

SELECT sid,SUM(score) AS sum_score
FROM sc
GROUP BY sid;

第二步:按总成绩排名(排名跳跃)

SELECT 
*,
rank() over (ORDER BY sum_score DESC) AS 排名
FROM (SELECT sid,SUM(score) AS sum_score
      FROM sc
      GROUP BY sid) AS a;

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

解题思路:

分析题目:“总分”、“不保留名次空缺”

第一步:计算总成绩

第二步:按总成绩排名(排名连续)

SELECT 
*,
dense_rank() over (ORDER BY sum_score DESC) AS 排名
FROM (SELECT sid,SUM(score) AS sum_score
      FROM sc
      GROUP BY SId) AS a;

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

解题思路:

分析题目关键词:“课程编号”、“课程名称”、“分数段”、“人数”

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

第二步:使用CASE WHEN,按照课程编号统计各分数段人数

SELECT
CId,
SUM(CASE WHEN score >= 0  AND score < 60 THEN 1 ELSE 0 END) AS '[0-60]',
SUM(CASE WHEN score >= 60 AND score < 70 THEN 1 ELSE 0 END) AS '[60-70]',
SUM(CASE WHEN score >= 70 AND score < 85 THEN 1 ELSE 0 END) AS '[70-85]',
SUM(CASE WHEN score >= 85 AND score <= 100 THEN 1 ELSE 0 END) AS '[85-100]'
FROM sc
GROUP BY CId;

第三步:转化成所占百分比

SELECT
CId,
SUM(CASE WHEN score >= 0  AND score < 60 THEN 1 ELSE 0 END) / COUNT(*) AS '[0-60]',
SUM(CASE WHEN score >= 60 AND score < 70 THEN 1 ELSE 0 END) / COUNT(*) AS '[60-70]',
SUM(CASE WHEN score >= 70 AND score < 85 THEN 1 ELSE 0 END) / COUNT(*) AS '[70-85]',
SUM(CASE WHEN score >= 85 AND score <= 100 THEN 1 ELSE 0 END) / COUNT(*) AS '[85-100]'
FROM sc
GROUP BY CId;

第四步:使用CONCAT拼接，输出百分比符号

SELECT
CId,
CONCAT(SUM(CASE WHEN score >= 0  AND score < 60 THEN 1 ELSE 0 END) / COUNT(*)*100,'%') AS '[0-60]',
CONCAT(SUM(CASE WHEN score >= 60 AND score < 70 THEN 1 ELSE 0 END) / COUNT(*)*100,'%') AS '[60-70]',
CONCAT(SUM(CASE WHEN score >= 70 AND score < 85 THEN 1 ELSE 0 END) / COUNT(*)*100,'%') AS '[70-85]',
CONCAT(SUM(CASE WHEN score >= 85 AND score <= 100 THEN 1 ELSE 0 END) / COUNT(*)*100,'%') AS '[85-100]'
FROM sc
GROUP BY CId;

第五步:关联课程表，输出课程名称 

SELECT a.*,b.Cname
FROM (SELECT
      CId,
      CONCAT(SUM(CASE WHEN score >= 0  AND score < 60 THEN 1 ELSE 0 END) / COUNT(*)*100,'%') AS '[0-60]',
      CONCAT(SUM(CASE WHEN score >= 60 AND score < 70 THEN 1 ELSE 0 END) / COUNT(*)*100,'%') AS '[60-70]',
      CONCAT(SUM(CASE WHEN score >= 70 AND score < 85 THEN 1 ELSE 0 END) / COUNT(*)*100,'%') AS '[70-85]',
      CONCAT(SUM(CASE WHEN score >= 85 AND score <= 100 THEN 1 ELSE 0 END) / COUNT(*)*100,'%') AS '[85-100]'
      FROM sc
      GROUP BY CId) AS a
LEFT JOIN course AS b
ON a.CId = b.CId;

结果展示:

18.查询各科成绩前三名的记录* 

解题思路:

分析题目关键词，“各科成绩”、“前三名”

第一步:各科成绩排名

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 <= 3;

扩展

SELECT a.*
FROM sc AS a
WHERE (SELECT COUNT(*) 
       FROM sc AS b 
       WHERE b.CId = a.CId AND b.score > a.score) < 3
ORDER BY CId DESC,score DESC;

 展示结果:

 19.查询每门课程被选修的学生数

解题思路:

分析题目的关键词，“每门课程”、“学生数”

第一步:锁定表，成绩表

第二步:按照课程编号进行分组统计

SELECT CId,COUNT(*) AS 学生数
FROM sc
GROUP BY CId;

结果展示:

20.查询出只选修两门课程的学生学号和姓名 

解题思路:

分析题目的关键词:“两门课程”、“学生学号和姓名”

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

第二步:统计每个学生选修课程数，并筛选出只选修2门课程的学生Sid

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

第三步:关联学生表，输出学生姓名

SELECT a.SId,b.Sname
FROM(SELECT SId,COUNT(*) AS CT
     FROM sc
     GROUP BY SId
     HAVING CT = 2) AS a
LEFT JOIN student AS b
ON a.SId = b.SId;

结果展示:

扩展

-- 另一解法
SELECT a.Sid, a.Sname 
FROM student AS a 
INNER JOIN sc AS b 
ON a.Sid = b.Sid 
GROUP BY a.Sid 
HAVING COUNT(b.Cid) = 2;

21.查询男生、女生人数

解题思路:

分析题目:“不同性别”，“学生数”

第一步:锁定表，学生表

第二步:按照性别进行分组统计

SELECT Ssex,COUNT(*) AS num
FROM student
GROUP BY Ssex;

结果展示:

22.查询名字中含有[风]字的学生信息 

解题思路:

分析题目:“名字中含有[风]”、“学生信息”

第一步:锁定表，学生表

第二步:使用LIKE操作符，%作为占位符

SELECT *
FROM student
WHERE Sname LIKE '%风%';

结果展示:

23.查询同名同性学生名单，并统计同名同性人数* 

解题思路:

分析题目:“同名同性”、“人数”

第一步:锁定表，学生表

第二步:自连接查询，筛选出名字、性别相同但学生编号不同的信息

SELECT a.*
FROM student AS a
INNER JOIN student AS b
ON a.Sname = b.Sname 
AND a.Ssex = b.Ssex
AND a.SId != b.SId;

第三步:统计同名同性的人数

SELECT Sname,Ssex,COUNT(*) AS num
FROM(SELECT a.*
     FROM student AS a
     INNER JOIN student AS b
     ON a.Sname = b.Sname 
     AND a.Ssex = b.Ssex
     AND a.SId != b.SId) AS c
GROUP BY Sname,Ssex;

展示结果:

24.查询1990年出生的学生名单 

解题思路:

分析题目的关键字:“出生日期Sage”

第一步:锁定表，学生表

SELECT * FROM student;

截取部分结果

 第二步:使用year(),筛选出1990年出生的学生

SELECT *
FROM student
WHERE YEAR(sage) = '1990';

-- 另一解法
SELECT *
FROM student
WHERE LEFT(sage,4) = '1990';

 展示结果:

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

解题思路:

分析题目关键词:“平均成绩”、“排序”

第一步:锁定表，成绩表

第二步:计算每门课程的平均成绩

SELECT CId,AVG(score) AS avg_score
FROM sc
GROUP BY CId;

第三步:排序(按照平均成绩降序、课程编号升序) 

SELECT CId,AVG(score) AS avg_score
FROM sc
GROUP BY CId
ORDER BY avg_score DESC,CId ASC;

结果展示:

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

解题思路:

分析题目关键词:“平均成绩”、“学号”、“姓名”

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

第二步:分组聚合求出每个学生平均成绩，并筛选出平均成绩大于等于85分的学生

SELECT SId,AVG(score) AS avg_score
FROM sc
GROUP BY SId
HAVING avg_score >= 85;

第三步:关联学生表，获取学生姓名 

SELECT a.sid,b.Sname,avg_score
FROM (SELECT SId,AVG(score) AS avg_score
      FROM sc
      GROUP BY SId
      HAVING avg_score >= 85) AS a
LEFT JOIN student AS b
ON a.SId = b.SId;

展示结果:

扩展

SELECT a.Sid, a.Sname, AVG(b.score)AS avg_score 
FROM student AS a 
INNER JOIN sc AS b 
ON a.Sid = b.Sid 
GROUP BY a.Sid 
HAVING avg_score >= 85;

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

解题思路:

分析题目关键词:“课程名称”、“分数”、“学生姓名”

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

第二步:求出课程名称为数学的课程编号

SELECT CId
FROM course
WHERE Cname = '数学';

第三步:成绩表关联学生表，查询数学分数低于60分的学生信息 

SELECT b.SId,b.Sname,a.CId,a.score
FROM sc AS a
LEFT JOIN student AS b
ON a.SId = b.SId
WHERE CId = (SELECT CId FROM course WHERE Cname = '数学')
AND score < 60;

展示结果:

 扩展

-- 另一解法
SELECT a.Sid, a.Sname, b.Cid, b.score 
FROM student AS a 
INNER JOIN sc AS b 
ON a.Sid = b.Sid 
INNER JOIN course AS c 
ON b.Cid = c.Cid 
WHERE c.Cname = '数学' 
AND b.score < 60;

28.查询所有学生的课程及分数情况(存在学生没成绩，没选课的情况) 

解题思路:

分析题目关键词，“所有学生”、“课程”、“分数”

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

第二步:学生表左连接成绩表

SELECT *
FROM student AS a
LEFT JOIN sc AS b
ON a.SId = b.SId;

展示结果:

29.查询任何一门课程成绩在70分以上的姓名、课程名称和分数 

 解题思路:

分析题目关键词:“学生姓名”、“课程名称”、“分数”

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

第二步:关联成绩表和课程表，并筛选出成绩在70分以上的记录

SELECT *
FROM sc AS a
LEFT JOIN course AS b
ON a.CId = b.CId
WHERE a.score > 70;

第三步:关联学生表，获取学生姓名 

SELECT c.Sname,b.Cname,a.score
FROM sc AS a
LEFT JOIN course AS b
ON a.CId = b.CId
LEFT JOIN student AS c
ON a.SId = c.SId
WHERE a.score > 70;

展示结果:

扩展:换成INNER JOIN也行 

30.查询不及格的课程

解题思路:

分析题目关键词:“不及格”、“课程”

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

第二步:筛选出分数小于60的课程Cid

SELECT DISTINCT CId
FROM sc
WHERE score < 60;

 

第三步:使用子查询，获取课程信息 

SELECT *
FROM course
WHERE CId IN (SELECT DISTINCT CId
              FROM sc
              WHERE score < 60);

展示结果:

扩展

-- 另一解法
SELECT DISTINCT a.Cid, a.Cname, a.Tid 
FROM course AS a 
INNER JOIN sc AS b 
ON a.Cid = b.Cid 
WHERE b.score < 60;