MySQL -- 07_最流行的查询需求分析（一些分组排序查询、开窗函数 dense_rank、distinct 去重函数 等~）

最流行的查询需求分析07

演示数据准备的SQL

演示数据准备的SQL

需求演示

36、查询每一门课程成绩都在70分以上的姓名、课程名称和分数

group by + min() + in() 函数

用 group by 对学生id分组，用 min() 函数查出每门课程都大于70分的学生，然后用 in 匹配符合条件的学生

-- 36、查询每一门课程成绩都在70分以上的学生的姓名、课程名称和分数 

SELECT
	st.s_name,
	co.c_name,
	sc.s_score 
FROM
	score sc
	LEFT JOIN course co ON co.c_id = sc.c_id
	LEFT JOIN student st ON st.s_id = sc.s_id 
WHERE
st.s_id in (

	-- 先查询出3个成绩都70分以上的学生的id
	select s_id from score group by s_id having min(s_score) >= 70

)

37、查询不及格的课程及学生

普通表连接查询

-- 37、查询不及格的课程及学生

SELECT
	st.s_name,
	co.c_name,
	sc.s_score 
FROM
	score sc
	LEFT JOIN course co ON sc.c_id = co.c_id
	LEFT JOIN student st ON st.s_id = sc.s_id 
WHERE
	sc.s_score < 60

38、查询课程编号为01语文且课程成绩在80分以上的学生的学号和姓名

普通表连接查询

-- 38、查询课程编号为01语文且课程成绩在80分以上的学生的学号和姓名


SELECT
	st.s_id,
	st.s_name,
	co.c_name,
	sc.s_score 
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 
WHERE
	co.c_id = '01' 
	AND sc.s_score >= 80

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

长型数据格式（多行）和宽型数据格式（多列）

-- 39、求每门课程的学生人数

-- 宽型数据格式

SELECT 
	sum(case when sc.c_id = '01' then 1 else 0 end) '语文',
	sum(case when sc.c_id = '02' then 1 else 0 end) '数学',
	sum(case when sc.c_id = '03' then 1 else 0 end) '英语'
FROM
	course co
	LEFT JOIN score sc ON co.c_id = sc.c_id




-- 长型数据格式

SELECT
	co.c_name, count(sc.s_id) '人数'
FROM
	course co
	LEFT JOIN score sc ON sc.c_id = co.c_id 
GROUP BY
	co.c_id

40、查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其所有成绩

子查询+limit

limit 1 返回查询结果的第一行数据

-- 40、查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其所有成绩


SELECT
	st.*,co.c_name,sc.s_score 
FROM
	student st
	LEFT JOIN score sc ON sc.s_id = st.s_id 
	LEFT JOIN course co ON co.c_id = sc.c_id
WHERE
	st.s_id = (
	SELECT
		sc.s_id
	FROM
		teacher te
		LEFT JOIN course co ON co.t_id = te.t_id
		LEFT JOIN score sc ON sc.c_id = co.c_id 
	WHERE
		te.t_name = '张三' 
	ORDER BY
		sc.s_score DESC 
		
		-- limit 1 返回查询结果的第一行数据
		LIMIT 1 
	)

41、查询不同课程但成绩相同的学生的学生编号、课程编号、学生成绩

distinct 去重

-- 41、查询不同课程但成绩相同的学生的学生编号、课程编号、学生成绩

SELECT 
  distinct s1.*,
	co.c_name,
	st.s_name
FROM
	score s1
	LEFT JOIN score s2 ON s1.c_id != s2.c_id
	LEFT JOIN course co ON co.c_id = s1.c_id
  LEFT JOIN student st ON st.s_id = s1.s_id  	
WHERE
	s1.s_score = s2.s_score

42、查询每门课程成绩最好的前两名

使用开窗函数 dense_rank() over()

使用这个开窗函数，查出排名后获取前两名就可以了，我这里连表是为了把数据展示的更完整清晰。

-- 42、查询每门课程成绩最好的前两名 

SELECT
	st.s_id,
	st.s_name,
	t.c_name,
	t.s_score,
	t.drk 
FROM
	student st
RIGHT JOIN ( 
	SELECT 
		sc.*, 
		co.c_name,
		dense_rank () over ( PARTITION BY sc.c_id ORDER BY sc.s_score DESC ) drk 
	FROM score sc
	LEFT JOIN course co on co.c_id = sc.c_id
) t ON t.s_id = st.s_id 
WHERE
t.drk IN (1,2)

子查询写法，不用开窗函数排序

和上面结果不一样是因为我这里只取前两名，没有考虑成绩并列相同的。
上面的开窗函数写法就有考虑成绩相同的并列排名

-- 子查询写法
SELECT
	* 
FROM
	score s1 
WHERE

  -- 这个子查询相当于上面的开窗函数
	( SELECT 
			count( s2.s_score ) 
		FROM score s2 
		WHERE 
		  s1.c_id = s2.c_id 
			AND s1.s_score < s2.s_score 
	) + 1 <= 2 
	
ORDER BY
	s1.c_id,
	s1.s_score DESC

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

group by … having … order by

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

SELECT
	sc.c_id '课程编号',
	count( 1 ) cnt 
FROM
	score sc 
GROUP BY
	sc.c_id 
HAVING
	count( 1 )>= 5 
ORDER BY
	cnt DESC,  -- 按人数降序排列
	sc.c_id ASC -- 按课程号升序排列

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

group by … having …

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

SELECT
	sc.s_id ,
	st.s_name,
	count( sc.s_id ) '选修课程数'
FROM
	score sc
	LEFT JOIN student st ON st.s_id = sc.s_id 
GROUP BY
	sc.s_id 
HAVING
	count( sc.s_id ) >= 2

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

group by … having …

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

SELECT
	st.* 
FROM
	student st
	LEFT JOIN score sc ON st.s_id = sc.s_id 
GROUP BY
	sc.s_id 
HAVING
	count( sc.s_id ) = ( SELECT count( 1 ) FROM course )