MySQL 给结果集分等级

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

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

一.需求

给表EMP中的工资分等级，并允许捆绑，返回下列结果集:

±----±--------+
| rnk | sal |
±----±--------+
| 1 | 800.00 |
| 2 | 950.00 |
| 3 | 1100.00 |
| 4 | 1250.00 |
| 4 | 1250.00 |
| 5 | 1300.00 |
| 6 | 1500.00 |
| 7 | 1600.00 |
| 8 | 2450.00 |
| 9 | 2850.00 |
| 10 | 2975.00 |
| 11 | 3000.00 |
| 11 | 3000.00 |
| 12 | 5000.00 |
±----±--------+

二.解决方案

窗口函数会使等级查询简单。如果暂不支持窗口函数，可以使用标量子查询

2.1 子查询方法

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

测试记录

mysql> select (select count( b.sal)
    ->          from emp b
    ->         where b.sal <= a.sal) as rnk,
    ->      a.sal
    ->   from emp a;
+------+---------+
| rnk  | sal     |
+------+---------+
|    1 |  800.00 |
|    8 | 1600.00 |
|    5 | 1250.00 |
|   11 | 2975.00 |
|    5 | 1250.00 |
|   10 | 2850.00 |
|    9 | 2450.00 |
|   13 | 3000.00 |
|   14 | 5000.00 |
|    7 | 1500.00 |
|    3 | 1100.00 |
|    2 |  950.00 |
|   13 | 3000.00 |
|    6 | 1300.00 |
+------+---------+
14 rows in set (0.00 sec)

2.2 MySQL 8.0 窗口函数方法

select dense_rank() over w as 'rnk', sal
  from emp
window w as (order by sal)
;

测试记录

mysql> select dense_rank() over w as 'rnk', sal
    ->   from emp
    -> window w as (order by sal)
    -> ;
+-----+---------+
| rnk | sal     |
+-----+---------+
|   1 |  800.00 |
|   2 |  950.00 |
|   3 | 1100.00 |
|   4 | 1250.00 |
|   4 | 1250.00 |
|   5 | 1300.00 |
|   6 | 1500.00 |
|   7 | 1600.00 |
|   8 | 2450.00 |
|   9 | 2850.00 |
|  10 | 2975.00 |
|  11 | 3000.00 |
|  11 | 3000.00 |
|  12 | 5000.00 |
+-----+---------+
14 rows in set (0.00 sec)