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)