20-30

20、查询学生的总成绩并进行排名

– 总成绩 sum ROW_number

SELECT DISTINCT
t.s_id,
ROW_NUMBER() over(ORDER BY t.s_id )
from 
(SELECT DISTINCT
	a.s_id,
	sum( a.s_score ) over ( PARTITION BY a.s_id ) AS sum_sc 
FROM
	score a ) t

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

-- 分组根据不同的老师 不同的课程  ，降序排序
-- 开窗函数不会导致结果集的行减少，因此会出现重复的行，可以使用 distinct 进行去重
SELECT 
	a.t_id,
	a.t_name,
	b.c_name,
	avg( c.s_score ) over ( PARTITION BY a.t_name ) avg_sc 
FROM
	teacher a
	LEFT JOIN course b ON a.t_id = b.t_id
	LEFT JOIN score c ON b.c_id = c.c_id 
ORDER BY
	avg_sc DESC

	
-- 不使用开窗

SELECT
	a.t_name,
	b.c_name,
	avg( c.s_score ) avg_s 
FROM
	teacher a
	LEFT JOIN course b ON a.t_id = b.t_id
	LEFT JOIN score c ON c.c_id = b.c_id 
GROUP BY
	a.t_name,b.c_name 
ORDER BY
	avg_s DESC

image-20240517220439855

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

– 所有同学的成绩和学生信息

SELECT
	t.* 
FROM
	(
	SELECT
		a.c_id,
		a.s_score,
		b.*,
		ROW_NUMBER() over ( PARTITION BY a.c_id ORDER BY a.s_score desc) AS sc_pm 
	FROM
		score a
		LEFT JOIN student b ON a.s_id = b.s_id 
	) t 
WHERE
	t.sc_pm in (2,3)

临时表

WITH RankedScores AS (
    SELECT
        s_id,
        c_id,
        s_score,
        ROW_NUMBER() OVER (PARTITION BY c_id ORDER BY s_score DESC) AS ranka
    FROM
        score
)
SELECT
    rs.c_id AS 课程ID,
    c.c_name AS 课程名称,
    rs.s_id AS 学生ID,
    s.s_name AS 学生姓名,
    rs.s_score AS 成绩
FROM
    RankedScores rs
JOIN
    Course c ON rs.c_id = c.c_id
JOIN
    Student s ON rs.s_id = s.s_id
WHERE
    rs.ranka in (2,3)

image-20240517221026273

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

– 按照课程进行分组，并且统计人数 ,要统计分段，包括0分的吗，没有成绩不算对吧

SELECT 
	a.c_id,	
	b.c_name,
	count( a.s_score ) 人数,

	sum( CASE WHEN a.s_score >= 85 AND a.s_score <= 100 THEN 1 ELSE 0 END ) '[100-85]',
	sum( CASE WHEN a.s_score >= 70 AND a.s_score < 85 THEN 1 ELSE 0 END ) '[85-70]',
	sum( CASE WHEN a.s_score >= 60 AND a.s_score < 70 THEN 1 ELSE 0 END ) '[70-60]',
	sum( CASE WHEN a.s_score >= 0 AND a.s_score < 60 THEN 1 ELSE 0 END )  '[0-60]',
	
	sum( CASE WHEN a.s_score >= 85 AND a.s_score <= 100 THEN 1 ELSE 0 END )/count(1) '[100-85]%',
	sum( CASE WHEN a.s_score >= 70 AND a.s_score < 85 THEN 1 ELSE 0 END )/count(1) '[85-70]%',
	sum( CASE WHEN a.s_score >= 60 AND a.s_score < 70 THEN 1 ELSE 0 END )/count(1)'[70-60]%',
	sum( CASE WHEN a.s_score >= 0 AND a.s_score < 60 THEN 1 ELSE 0 END )/count(1) '[0-60]%'
FROM
	score a
	LEFT JOIN course b on a.c_id = b.c_id 
GROUP BY
	a.c_id,b.c_name

image-20240517221436073

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

– 学生平均成绩

SELECT
	t.s_id,
	t.avg_sc,
	ROW_NUMBER() over (ORDER BY t.avg_sc DESC ) 排名 
FROM
	( SELECT a.s_id, avg( a.s_score ) avg_sc FROM score a GROUP BY a.s_id ) t
		

SELECT
t1.s_id,
    t1.avg_sc,
	(
	SELECT
		COUNT(*) 
	FROM
		( SELECT s_id, AVG( s_score ) AS avg_sc FROM score GROUP BY s_id ) AS t2 
	WHERE
		t2.avg_sc > t1.avg_sc 
	) + 1 AS 排名 
FROM
	( SELECT s_id, avg( score.s_score ) avg_sc FROM score GROUP BY s_id ) t1
	
	ORDER BY 排名

解释

主查询:
t1 是子查询的别名，它包含每个学生的平均成绩。
使用相关子查询来计算每个学生的排名。
相关子查询:
对于每一行，在 t1 中使用一个子查询 t2 计算比当前学生平均成绩高的学生数量。
子查询 t2 计算比当前学生 t1 的平均成绩高的学生的数量。
由于排名从 1 开始，所以需要 + 1。
ORDER BY:
按排名进行排序。

image-20240517222645107

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

– 各科成绩前三 between and – 查询出所有的成绩，按照课程分类，分数降序排列

SELECT
t.c_id 课程id,
	t.s_id 学生id,
	t.cj_pm 排名 
	FROM
	-- 第一步 找出各科成绩排名
	(
	SELECT
		s.c_id,
		s.s_id,
		s.s_score,
		ROW_NUMBER() over ( PARTITION BY s.c_id ORDER BY s.s_score DESC ) cj_pm 
	FROM
		score s 
	) t
	where t.cj_pm
	in (1,2,3)

image-20240517223057592

26、查询每门课程被选修的学生数

– 课程被多少学生选 count

SELECT

b.c_name,
	count( s_id ) 
FROM
	score a
	RIGHT JOIN course b ON b.c_id = a.c_id 
GROUP BY
	b.c_id,b.c_name

image-20240517223208988

27、查询出只有两门课程的全部学生的学号和姓名

– 只选了两门课程的学生学号和姓名 – score student count

SELECT
	a.s_id,
	c.s_name,
	count( a.s_id ) count_c 
FROM
	score a
	LEFT JOIN course b ON a.c_id = b.c_id
	LEFT JOIN student c ON a.s_id = c.s_id 
	GROUP BY a.s_id,c.s_name
HAVING
	count_c = 2

image-20240517223412015

28、查询男生、女生人数

– student

SELECT
	count( a.s_id ) 
FROM
	student a 
GROUP BY
	a.s_sex

29、查询名字中含有"风"字的学生信息

– 含有 %like%

SELECT
	a.* 
FROM
	student a 
WHERE
	a.s_name LIKE '%风%'

30、查询同名同性学生名单，并统计同名人数

– 同名同姓 count

SELECT
	a.s_name,
	count( a.s_id ) 
FROM
	student a,
	student b 
WHERE
	a.s_sex = b.s_sex 
	AND a.s_name = b.s_name 
	-- 确保自连接市不要连接到自己了
	AND a.s_id != b.s_id 
GROUP BY
	a.s_name	

	SELECT
	s_name AS 姓名,
	s_sex AS 性别,
COUNT(*) AS 同名人数
FROM
    Student
GROUP BY
    s_name, s_sex
HAVING
    COUNT(*) > 1;