mysql前n名_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)