练习36-39：窗口函数，聚合查询，子查询

最新推荐文章于 2024-07-12 20:25:05 发布

百里守约学编程

最新推荐文章于 2024-07-12 20:25:05 发布

阅读量182

点赞数

分类专栏： # MySQL练习经典45题文章标签： sql 数据库 mysql

本文链接：https://blog.csdn.net/YouMing_Li/article/details/126635015

版权

MySQL练习经典45题专栏收录该内容

28 篇文章 11 订阅

订阅专栏

四张表信息

在这里插入图片描述

36. 查询每门科目成绩最好的前两名，允许出现并列的名次

第一步：看到排名，且允许名次并列，很容易想到窗口函数dense_rank()

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

在这里插入图片描述

第二步：选出rk <= 2的即可

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

在这里插入图片描述

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

简单分组聚合查询

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

在这里插入图片描述

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

第一步：按学生编号进行分组，去重聚合课程数量

 SELECT
 sid,
 COUNT(DISTINCT cid)
 FROM sc
 GROUP BY sid;

在这里插入图片描述

第二步：筛选出选修课程大于两门的，结果只保留学生学号

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

在这里插入图片描述

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

第一步：锁定表，课程表，成绩表，学生表
第二步：查询课程总数

 SELECT
 COUNT(*) AS ct
 FROM course;

在这里插入图片描述

第三步：查询每个学生所学课程的去重总数,筛出数量和总课程数相等的学生id

 SELECT
 sid
 FROM sc
 GROUP BY sid
 HAVING COUNT(DISTINCT cid) = (SELECT COUNT(*) AS ct FROM course);

在这里插入图片描述

第四步：关联学生表，或者用in作为子查询查出学生信息
关联学生表写法

 SELECT
 b.*
 FROM (
	 SELECT
	 sid
	 FROM sc
	 GROUP BY sid
	 HAVING COUNT(DISTINCT cid) = (SELECT COUNT(*) AS ct FROM course)
 ) a
 JOIN student b
 ON a.sid = b.sid;

在这里插入图片描述

子查询写法

 SELECT
 * 
 FROM student
 WHERE sid IN (
	SELECT
	 sid
	 FROM sc
	 GROUP BY sid
	 HAVING COUNT(DISTINCT cid) = (SELECT COUNT(*) AS ct FROM course)
 );