MySQL 选择前n个记录

╰半橙微兮° 2022-12-20 03:29 109阅读 0赞

备注:测试数据库版本为MySQL 8.0

如需要scott用户下建表及录入数据语句，可参考:  
[scott建表及录入数据sql脚本][scott_sql]

# 一.需求 #

一某种排序方式，限定结果集只包含一定数目的记录。  
例如，返回最高5档工资的员工姓名和工资。

# 二.解决方案 #

这种解决方案的关键是两个步骤: 首先按预定方式给行排序，然后限定结果集，只包含感兴趣的行。

## 2.1 标量子查询方法 ##

使用标量子查询，为每个工资创建一个等级。然后利用等级限制子查询的结果:

select ename,sal
      from (
    select ( select count(distinct b.sal)
               from emp b
             where a.sal <= b.sal) as rnk,
           a.sal,
           a.ename
      from emp a
            ) x
    where rnk <= 5;

测试记录:

mysql> select ename,sal
        ->   from (
        -> select ( select count(distinct b.sal)
        ->            from emp b
        ->          where a.sal <= b.sal) as rnk,
        ->        a.sal,
        ->        a.ename
        ->   from emp a
        ->         ) x
        -> where rnk <= 5;
    +-------+---------+
    | ename | sal     |
    +-------+---------+
    | JONES | 2975.00 |
    | BLAKE | 2850.00 |
    | CLARK | 2450.00 |
    | SCOTT | 3000.00 |
    | KING  | 5000.00 |
    | FORD  | 3000.00 |
    +-------+---------+
    6 rows in set (0.00 sec)

## 2.2 MySQL 8.0 窗口函数方法 ##

MySQL窗口函数功能更强大，代码更简洁

select ename,sal
      from (
    select ename,sal,
           dense_rank() over w  as 'dr'
      from emp
      window w as (order by sal desc) 
      ) x
    where dr <= 5
    ;

测试记录:

mysql> select ename,sal
        ->   from (
        -> select ename,sal,
        ->        dense_rank() over w  as 'dr'
        ->   from emp
        ->   window w as (order by sal desc)
        ->   ) x
        -> where dr <= 5
        -> ;
    +-------+---------+
    | ename | sal     |
    +-------+---------+
    | KING  | 5000.00 |
    | SCOTT | 3000.00 |
    | FORD  | 3000.00 |
    | JONES | 2975.00 |
    | BLAKE | 2850.00 |
    | CLARK | 2450.00 |
    +-------+---------+
    6 rows in set (0.00 sec)

[scott_sql]: https://www.jianshu.com/p/532fe68924cb