MySQL 转置已分等级的结果集

客官°小女子只卖身不卖艺 2022-11-04 01:38 171阅读 0赞

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

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

# 一.需求 #

给表中的值分等级，然后把结果集转置为三列，其思想是将最高的3挡作为一列、次高的3挡作一列，其余作一列。

例如，祥哥表EMP中的员工按SAL分等级，然后把结果集转置为三列。

其结果集如下所示:  
![image.png][]

# 二.解决方案 #

这个解决方案的关键是先使用窗口函数 DENSE\_RANK OVER，按SAL给员工分等级，同时允许捆绑。  
使用DENSE\_RANK OVER，可以很容易地看到最高的三档工资、接下来的三档工资，以及其与所有工资。  
下一步，使用窗口函数 ROW\_NUMBER OVER，给组内的员工分等级(最高组、次高组，及其余组)。  
之后，只要进行标准转换，或使用平台支持的内置字符串函数，美化结果即可。

select  max(case grp when 1 then concat(rpad(ename,6,' '),
                   ' (',sal,')') end) top_3,
            max(case grp when 2 then concat(rpad(ename,6,' '),
                   ' (',sal,')') end) next_3,
            max(case grp when 3 then concat(rpad(ename,6,' '),
                   ' (',sal,')') end) rest
      from  (
     select  ename,
             sal,
             rnk,
             case when rnk <= 3 then 1
                  when rnk <= 6 then 2
                  else               3
             end grp,
             row_number() over w2 as 'grp_rnk'
       from  (
     select  ename,
             sal,
             dense_rank() over w1 as 'rnk'
      from   emp
     window  w1 as (order by sal desc)
             ) x
      window w2 as (partition by case when rnk <= 3 then 1
                                      when rnk <= 6 then 2
                                      else               3
                                  end
                         order by sal desc, ename)
             ) y
      group  by grp_rnk ;

测试记录:

mysql> select  max(case grp when 1 then concat(rpad(ename,6,' '),
        ->                ' (',sal,')') end) top_3,
        ->         max(case grp when 2 then concat(rpad(ename,6,' '),
        ->                ' (',sal,')') end) next_3,
        ->         max(case grp when 3 then concat(rpad(ename,6,' '),
        ->                ' (',sal,')') end) rest
        ->   from  (
        ->  select  ename,
        ->          sal,
        ->          rnk,
        ->          case when rnk <= 3 then 1
        ->               when rnk <= 6 then 2
        ->               else               3
        ->          end grp,
        ->          row_number() over w2 as 'grp_rnk'
        ->    from  (
        ->  select  ename,
        ->          sal,
        ->          dense_rank() over w1 as 'rnk'
        ->   from   emp
        ->  window  w1 as (order by sal desc)
        ->          ) x
        ->   window w2 as (partition by case when rnk <= 3 then 1
        ->                                   when rnk <= 6 then 2
        ->                                   else               3
        ->                               end
        ->                      order by sal desc, ename)
        ->          ) y
        ->   group  by grp_rnk ;
    +------------------+------------------+------------------+
    | top_3            | next_3           | rest             |
    +------------------+------------------+------------------+
    | KING   (5000.00) | BLAKE  (2850.00) | TURNER (1500.00) |
    | FORD   (3000.00) | CLARK  (2450.00) | MILLER (1300.00) |
    | SCOTT  (3000.00) | ALLEN  (1600.00) | MARTIN (1250.00) |
    | JONES  (2975.00) | NULL             | WARD   (1250.00) |
    | NULL             | NULL             | ADAMS  (1100.00) |
    | NULL             | NULL             | JAMES  (950.00)  |
    | NULL             | NULL             | SMITH  (800.00)  |
    +------------------+------------------+------------------+
    7 rows in set (0.00 sec)

[scott_sql]: https://www.jianshu.com/p/532fe68924cb
[image.png]: /images/20221023/7b84a63227d346ae92bb748f0d30a242.png