MySQL 转置已分等级的结果集

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

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

scott建表及录入数据sql脚本

一.需求

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

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

其结果集如下所示:

二.解决方案

这个解决方案的关键是先使用窗口函数 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)