MySQL -- 05_最流行的查询需求分析（涉及子查询、开窗函数dense_rank和rank的区别、case when、round()、in、between 等~~~ ）

_L_J_H_

已于 2024-04-02 22:20:00 修改

阅读量642

点赞数 9

分类专栏： MySQL 文章标签： mysql 数据库

于 2024-04-01 23:52:56 首次发布

本文链接：https://blog.csdn.net/weixin_44411039/article/details/137225077

版权

MySQL 专栏收录该内容

14 篇文章 4 订阅

订阅专栏

最流行的查询需求分析05

演示数据准备的SQL

需求演示

21、查询不同老师所教不同课程平均分从高到低显示

普通分组排序写法

在这里插入图片描述

-- 21、查询不同老师所教不同课程平均分从高到低显示

-- 普通分组排序写法：

SELECT
	te.*,
	co.c_name,
	ROUND(AVG( sc.s_score ),1) avg_s 
FROM
	teacher te
	LEFT JOIN course co ON te.t_id = co.t_id
	LEFT JOIN score sc ON sc.c_id = co.c_id 
GROUP BY
	te.t_id 
ORDER BY
	AVG( sc.s_score ) DESC

22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩

即语文数学英语成绩都放在一起的第二和第三名

开窗函数 rank() + 子查询写法

在这里插入图片描述

-- 22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩
-- 即语文数学英语成绩都放在一起的第二和第三名

-- 2、然后主查询这里再根据条件获取数据
SELECT
	* 
FROM
	(
	-- 1、先用子查询，把分数排名排好序
	SELECT
		st.s_name,
		sc.c_id,
		co.c_name,
		sc.s_score,
		rank () over ( ORDER BY sc.s_score DESC ) rk 
	FROM
		course co
		LEFT JOIN score sc ON sc.c_id = co.c_id
		LEFT JOIN student st ON st.s_id = sc.s_id 
	) t 
WHERE
	t.rk IN ( 2, 3 )

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

between 注意点

between 10 and 20 ，查出来的数据是包括 10 和 20。

在这里插入图片描述

使用到 round()、sum、case when、count()

要多列来显示的话，直接用 case when 堆起来

在这里插入图片描述

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

SELECT
	co.c_id,co.c_name,
	ROUND(sum( CASE WHEN sc.s_score BETWEEN 86 AND 100 THEN 1 ELSE 0 END ),2) AS "[100-86]",
	ROUND(sum( CASE WHEN sc.s_score BETWEEN 70 AND 85 THEN 1 ELSE 0 END ),2) AS "[85-70]",
	ROUND(sum( CASE WHEN sc.s_score BETWEEN 60 AND 69 THEN 1 ELSE 0 END ),2) AS "[69-60]",
	ROUND(sum( CASE WHEN sc.s_score BETWEEN 0 AND 59 THEN 1 ELSE 0 END ),2) AS "[0-59]",
	ROUND(sum( CASE WHEN sc.s_score BETWEEN 86 AND 100 THEN 1 ELSE 0 END )/ count( 1 ),2) AS "[100-86]%",
	ROUND(sum( CASE WHEN sc.s_score BETWEEN 70 AND 85 THEN 1 ELSE 0 END )/ count( 1 ),2) AS "[85-70]%",
	ROUND(sum( CASE WHEN sc.s_score BETWEEN 60 AND 69 THEN 1 ELSE 0 END )/ count( 1 ),2) AS "[69-60]%",
	ROUND(sum( CASE WHEN sc.s_score BETWEEN 0 AND 59 THEN 1 ELSE 0 END )/ count( 1 ),2) AS "[0-59]%" 
FROM
	score sc
	left join course co on sc.c_id = co.c_id
	GROUP BY sc.c_id

24、查询学生平均成绩及其名次

先子查询，再使用开窗函数 rank() over 来排序和加上排名

先查询出每个学生的平均成绩作为子表数据，然后再用rank开窗函数，给这些数据进行排名和排序。

在这里插入图片描述

注意：

这种写法是错误的，使用 AVG() 函数会导致问题，因为它是一个聚合函数，不适合在开窗函数的 ORDER BY 子句中使用。

这样写只能查出一条数据，明显是错误的。

在这里插入图片描述

25、查询各科成绩前三名的记录

dense_rank() 函数 + 子查询 + in关键字的写法

在这里插入图片描述

dense_rank() 函数和 rank() 函数的区别

RANK() 函数会产生间隙，而 DENSE_RANK() 函数不会产生间隙，所有排名都是连续的

在这里插入图片描述

-- 25、查询各科成绩前三名的记录
SELECT
	st.s_id,
	st.s_name,
	co.c_name,
	t.s_score,
	t.rk '名次' 
FROM
	( SELECT *, dense_rank () over ( PARTITION BY c_id ORDER BY s_score DESC ) rk FROM score ) t
	LEFT JOIN student st ON st.s_id = t.s_id
	LEFT JOIN course co ON co.c_id = t.c_id 
WHERE
	t.rk IN ( 1, 2, 3 )