经典sql题（四）查找学生成绩排名第二的学科

查找每个学生的第二高分数及其学科

本文将通过一个示例，展示如何使用 SQL 中的窗口函数来找出每个学生的第二高分数及其对应的学科。

示例数据

假设我们的 test 表有以下数据：

student	subject	score
Alice	Math	85
Alice	Science	90
Bob	Math	80
Bob	Science	80
Bob	English	75
Charlie	Math	70
Charlie	Science	75
Charlie	History	80
Tom	Math	88
Tom	English	92
Lucy	Science	85
Lucy	Math	90

第一步：为每个学生的分数生成排名

我们首先需要为每个学生的分数生成一个排名，以便后续筛选。可以使用以下 SQL 查询：

SELECT 
    student,
    subject,
    score,
    DENSE_RANK() OVER (PARTITION BY student ORDER BY score DESC) AS rn
FROM 
    test;

结果（子查询结果）：

student	subject	score	rn
Alice	Science	90	1
Alice	Math	85	2
Bob	Math	80	1
Bob	Science	80	1
Bob	English	75	2
Charlie	History	80	1
Charlie	Science	75	2
Charlie	Math	70	3
Tom	English	92	1
Tom	Math	88	2
Lucy	Math	90	1
Lucy	Science	85	2

解析

• DENSE_RANK() 函数为每个学生的分数生成一个排名，分数高的排名小。
• PARTITION BY student 表示我们按照学生进行分区。
• ORDER BY score DESC 指定按照分数降序排列。

第二步：筛选出第二高分数的学科

接下来，我们将通过外层查询筛选出排名为 2 的记录：

SELECT 
    student,
    subject
FROM (
    SELECT 
        student,
        subject,
        score,
        DENSE_RANK() OVER (PARTITION BY student ORDER BY score DESC) AS rn
    FROM 
        test
) AS ranked_scores
WHERE rn = 2;

结果（最终结果）：

student	subject
Alice	Math
Bob	English
Charlie	Science
Tom	Math
Lucy	Science

解析

• 在外层查询中，我们只保留排名为 2 的记录，这样就得到了每个学生的第二高分数对应的学科。

完整步骤解析

1. 生成分数排名：

   • 查询：为每个学生的分数生成排名，以便后续筛选。
   • 结果表：显示学生的分数、学科及其对应的排名。

2. 筛选第二高分数的学科：

   • 查询：在生成的排名中筛选出排名为 2 的记录。
   • 最终结果：展示符合条件的学生和学科。

拓展

对比四个不同的窗口函数：RANK(), DENSE_RANK(), ROW_NUMBER(), 和 PERCENT_RANK()。

各种窗口函数的查询

我们将为每个学生的分数生成排名，并使用四个不同的窗口函数。

SELECT 
    student,
    subject,
    score,
    RANK() OVER (PARTITION BY student ORDER BY score DESC) AS rn,
    DENSE_RANK() OVER (PARTITION BY student ORDER BY score DESC) AS dense_rn,
    ROW_NUMBER() OVER (PARTITION BY student ORDER BY score DESC) AS row_rn,
    PERCENT_RANK() OVER (PARTITION BY student ORDER BY score DESC) AS percent_rn
FROM 
    test;

各窗口函数结果

student	subject	score	rn	dense_rn	row_rn	percent_rn
Alice	Science	90	1	1	1	0.00
Alice	Math	85	2	2	2	0.50
Bob	Math	80	1	1	1	0.00
Bob	Science	80	1	1	2	0.00
Bob	English	75	3	2	3	0.67
Charlie	History	80	1	1	1	0.00
Charlie	Science	75	2	2	2	0.50
Charlie	Math	70	3	3	3	1.00
Tom	English	92	1	1	1	0.00
Tom	Math	88	2	2	2	0.50
Lucy	Math	90	1	1	1	0.00
Lucy	Science	85	2	2	2	0.50

结果解析

1. RANK()：

   • 相同分数的学生会获得相同的排名，但接下来的排名会跳过。
   • 例如，Bob 在 Math 和 Science 中都获得了相同的分数 80，因此他们的排名都是 1，接下来的排名是 3。

2. DENSE_RANK()：

   • 与 RANK() 相似，但不会跳过排名。
   • Bob 在 Math 和 Science 中的排名都是 1，接下来的排名是 2。

3. ROW_NUMBER()：

   • 为每一行分配一个唯一的序号，即使分数相同。
   • Bob 在 Math 和 Science 中的排名是 1 和 2，尽管他们的分数相同。

4. PERCENT_RANK()：

   • 计算相对排名，值在 0 到 1 之间，表示该行在分区中的位置。
   • 例如，Alice 的 Math 分数位于所有分数的中间，因此她的 percent_rank 是 0.50。