MySQL 选择前n个记录

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

如需要scott用户下建表及录入数据语句，可参考:
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)