MySQL 给结果集分等级

Love The Way You Lie 2022-12-20 11:25 91阅读 0赞

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

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

[scott_sql]: https://www.jianshu.com/p/532fe68924cb