备注:测试数据库版本为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)
