526互联

SQL如何查找第N高的数据

发布时间 2024-01-03 23:10:59作者: 我是球啊

参考链接：https://leetcode.cn/problems/second-highest-salary/solutions/66337/tu-jie-sqlmian-shi-ti-ru-he-cha-zhao-di-ngao-de-sh/

https://blog.csdn.net/WHYbeHERE/article/details/125654403

题目：现在有课程表，记录了学生选修课程的名称以及成绩

现在需要找出语文课中成绩第二高的学生成绩，如果不存在第二高的成绩，就返回null。

解题思路

1. 找出所有选修了“语文课”的学生的成绩

select * 
from 成绩表
where 课程='语文';

2. 查找语文课程成绩的第二名

考虑到成绩可能有一样的值，所以使用district进行去重。

思路一：使用子查询找出语文成绩查询最大的成绩记为a，然后再找出小于a的最大值就是第二高的成绩。

select max(distinct 成绩) 
from 成绩表
where 课程='语文';

然后再找出小于a的最大值就是第二高的成绩

select max(distinct 成绩) 
from 成绩表
where 课程='语文' and
      成绩 < (select max(distinct 成绩) 
              from 成绩表 
              where 课程='语文');

思路二：使用limit和offset

limit n子句表示查询结果返回前n条数据

offset n表示跳过x条语句

limit y offset x分句表示查询结果跳过x条数据，读取前y条数据

使用limit和offset，降序排列再返回第二条记录就可以得到最大的值

select distinct 成绩  
from 成绩表
where 课程='语文'
order by 课程,成绩 desc
limit 1,1;

3. 考虑到特殊情况

题目要求，如果没有第二高的成绩，就返回空值，所以这里用判断空值的函数ifnull函数来处理特殊情况。

ifnull(a,b)函数：

如果value1不是空，结果返回a

如果value1是空，结果返回b

select ifnull(
(select max(distinct 成绩) from 成绩表
where 成绩<(select max(成绩) from 成绩表 where 课程='语文')
and 课程='语文')
,null) as '语文课第二名成绩';

本题考点

1. limit用法

2. ifnull用法

接下来我们总结一下“查找第N高的数”这类题怎么做

方法一：MAX

使用MAX找出最大值，同时使用两个MAX查询，其中一个作为筛选条件。剔除最大值，剩下的第二个MAX就是第二高的数。

SELECT MAX( salary ) SecondHighestSalary  
FROM Employee 
WHERE salary <( SELECT MAX( salary ) FROM Employee )

为什么这个代码可以应对只有一行的情况呢？让我们一起看一下MAX函数的定义

MAX函数返回expr列的最大值，此外，除了作用于数值型的列，还可以作用于字符串类型的列。当MAX函数应用于字符串类型的列时，它会返回具有最大字典顺序的字符串。

DISTINCT关键字可以用于查找expr不同值的最大值，然而，返回的结果与不加DISTINCT是一样的。

如果没有匹配的行，或者expr列是空的，MAX()会返回NULL。

文中还提到了窗口函数（Window Function）以及与之相关的 over_clause。让我们逐步解释这两个概念：

  1. Window Function:

    • 一个窗口函数是对查询结果集中的一组行执行计算的函数。与普通的聚合函数不同，窗口函数可以通过定义窗口（一组行的子集）来计算结果，而不仅仅是整个结果集。这使得窗口函数能够提供更灵活的计算方式，例如在特定的窗口范围内计算聚合值。

  2. over_clause:

    • over_clause 是窗口函数中的一个子句，用于指定窗口的定义。它规定了在窗口函数计算中使用的行子集。over_clause 的具体语法和用法可以在数据库管理系统的文档中找到。通常，它可以包括窗口的排序规则、分区规则等信息。

因此，如果在MAX函数中使用了 over_clause，那么该MAX函数将被视为窗口函数，其计算将基于窗口的定义。窗口函数的特点是它可以在查询结果的特定行子集上执行计算，而不仅仅是整个结果集。

然而，注意到文中提到 over_clause 不能与 DISTINCT 一起使用。这表示在使用窗口函数时，不能同时使用 DISTINCT 关键字。DISTINCT用于返回唯一的结果行，但由于窗口函数已经允许在行的子集上执行计算，与DISTINCT一起使用可能导致模糊性或不一致性的结果。因此，这两者通常不能同时使用。

mysql> SELECT student_name, MIN(test_score), MAX(test_score)
       FROM student
       GROUP BY student_name;

MySQL在比较ENUM和SET列时使用的是它们的字符串值，而不是使用这些字符串在ENUM或SET中的相对位置进行比较。

让我们分解这个说明：

  1. ENUM和SET列：

    • 在MySQL中，ENUM和SET是两种特殊的数据类型。ENUM表示枚举，SET表示集合。这两种类型都包含一组预定义的字符串值。

  2. MAX()函数比较：

    • 当使用MAX()函数来比较ENUM和SET列时，MySQL使用字符串值进行比较。这意味着它将考虑这些列中的字符串内容而不是这些字符串在ENUM或SET中的位置。

  3. ORDER BY的不同行为：

      • 与MAX()函数不同，当使用ORDER BY对ENUM和SET列进行排序时，MySQL通常会按照这些列中的相对位置进行比较。这就是说，在排序中，它会考虑ENUM或SET中的值的定义顺序。

假设有一个ENUM列定义如下：

CREATE TABLE example_table (
    status ENUM('Pending', 'Approved', 'Rejected')
);

如果表中有以下行：

+--------+
| status |
+--------+
| Approved |
| Pending  |
| Rejected |
+--------+

使用MAX(status)将返回"Rejected"，因为在字符串值的比较中，"Rejected"大于"Pending"和"Approved"。

降序排列是根据 ENUM 类型的内部顺序来进行的。在 MySQL 中，ENUM 类型的内部顺序是根据它们在列定义中出现的顺序确定的。在创建 example_table 表时，ENUM 类型的值的顺序是 'Pending', 'Approved', 'Rejected'。因此，当你使用 ORDER BY status DESC 时，它按照 'Rejected', 'Approved', 'Pending' 的顺序降序排列。

因此，由上述MAX()在没有符合的结果时返回空，就可以满足记录只有一行返回空的情况了。

方法二：limit offset

使用子查询，对salary进行降序排序之后，通过limit 1 offset 1找出第二高的数。

SELECT(
      (SELECT DISTINCT Salary    # distinct不能漏掉，排除重复值
       FROM Employee
       ORDER BY Salary DESC
        LIMIT 1 OFFSET 1)
) AS SecondHighestSalary

limit/offset语法：

1. offset X是跳过X个数据，limit Y是选取Y个数据

2. limit X，Y中表示跳过X个数据，读取Y个数据

有些同学可能会疑惑，为什么要写两个select，不直接使用一个select呢？原因在于题目要求在只有一行记录的时候返回NULL，我们可以建立一个空表做一个实验：

可以看出，我们使用limit 1时，返回一个空结果集合，不显示NULL，也就是说，返回的结果为0行。

如果使用MAX函数进行选择，可以发现返回一行结果，内容为NULL，与我们上边讲的也一致。

然后我们再来用函数判断一下，可以看到返回结果123，这是为什么呢，子查询返回的是一个空结果集合。

空结果集和 NULL 是在关系数据库中两个不同的概念。

  1. 空结果集（Empty Result Set）：

    • 定义： 当执行一个查询时，如果查询条件没有匹配的行，那么结果就是一个空结果集。
    • 示例： 如果你查询一个表，但没有符合条件的行，那么结果是一个空结果集。
    • 影响： 空结果集表示没有找到符合查询条件的数据，但查询本身是合法的。

  2. NULL：

    • 定义： NULL 是一个特殊的数据库值，表示缺失的、未知的或不适用的数据。
    • 示例： 如果某一列的值没有被明确设置，或者是不适用的，那么它就是 NULL。
    • 影响： NULL 表示缺少具体的数据值，而不同于具体的数值或空字符串。

在你的问题中，如果子查询返回一个空结果集，那意味着查询条件没有匹配的行，但查询本身是有效的。COALESCE 函数在这种情况下会返回它的第二个参数，即 123。这是处理空结果集时提供的一种方式，而不是返回 NULL 值。如果子查询返回 NULL，则 COALESCE 会返回其第一个非空参数，或者如果所有参数都是 NULL，则返回 NULL。

所以在使用第二种方法的时候，需要使用子查询嵌套。

可以看出嵌套后的查询可以返回NULL，否则就是0行的空集合。

或者只使用limit：

SELECT (      
SELECT DISTINCT Salary   
FROM Employee
 ORDER BY Salary DESC
LIMIT 1,1
)AS SecondHighestSalary

方法三：窗口函数dense_rank

select 
(
    select 
    salary 
    from 
        (select salary, dense_rank() over (order by salary desc) rn from Employee) t 
    where t.rn=2 limit 1
) SecondHighestSalary;

有的答案会在最外层使用ifnull()。 这个其实可以省略，因为直接用select时，默认查不到临时表会返回null。

先讲到这吧，其实我还是没懂（?）