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)