转置已分等级的结果集

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

例如给EMP表中的员工按SAL分等级，结果如下所示

SQL> select max(case grp when 1 then rpad(ename,6)||'('||sal||')' end) top_3,
  2  max(case grp when 2 then rpad(ename,6)||'('||sal||')' end) next_3,
  3  max(case grp when 3 then rpad(ename,6)||'('||sal||')' end) rest
  4  from (
  5  select ename,sal,rnk,
  6  case when rnk <= 3 then 1
  7  when rnk <= 6 then 2
  8  else 3
  9  end grp,
 10  row_number() over(
 11  partition by case when rnk <= 3 then 1
 12  when rnk <= 6 then 2
 13  else 3
 14  end order by sal desc,ename
 15  ) grp_rnk
 16  from(
 17  select ename,sal,dense_rank() over(order by sal desc) rnk
 18  from emp
 19  ) x
 20  ) y
 21  group by grp_rnk;

TOP_3                NEXT_3               REST
-------------------- -------------------- --------------------
KING  (5000)         BLAKE (2850)         TURNER(1500)
                                          JAMES (950)
FORD  (3000)         CLARK (2450)         MILLER(1300)
JONES (2975)                              WARD  (1250)
                                          ADAMS (1100)
SCOTT (3000)         ALLEN (1600)         MARTIN(1250)
                                          SMITH (800)

7 rows selected.

 

下面我们从内到外解剖一下这个查询，首先来看一下内联视图x的结果

SQL> select ename,sal,dense_rank() over(order by sal desc) rnk
  2  from emp;

ENAME             SAL        RNK
---------- ---------- ----------
KING             5000          1
FORD             3000          2
SCOTT            3000          2
JONES            2975          3
BLAKE            2850          4
CLARK            2450          5
ALLEN            1600          6
TURNER           1500          7
MILLER           1300          8
WARD             1250          9
MARTIN           1250          9

ENAME             SAL        RNK
---------- ---------- ----------
ADAMS            1100         10
JAMES             950         11
SMITH             800         12

14 rows selected.

 

这一步只是给员工按照工资分了等级，下一步，使用case表达式来判断dense_rank求出的等级，并据此对内联视图x的结果集创建组。下面再来看一下内联视图y的结果

SQL> select ename,sal,rnk,
  2  case when rnk <= 3 then 1
  3  when rnk <= 6 then 2
  4  else 3
  5  end grp,
  6  row_number() over(
  7  partition by case when rnk <= 3 then 1
  8  when rnk <= 6 then 2
  9  else 3
 10  end order by sal desc,ename
 11  ) grp_rnk
 12  from(
 13  select ename,sal,dense_rank() over(order by sal desc) rnk
 14  from emp
 15  ) ;

ENAME             SAL        RNK        GRP    GRP_RNK
---------- ---------- ---------- ---------- ----------
KING             5000          1          1          1
FORD             3000          2          1          2
SCOTT            3000          2          1          3
JONES            2975          3          1          4
BLAKE            2850          4          2          1
CLARK            2450          5          2          2
ALLEN            1600          6          2          3
TURNER           1500          7          3          1
MILLER           1300          8          3          2
MARTIN           1250          9          3          3
WARD             1250          9          3          4

ENAME             SAL        RNK        GRP    GRP_RNK
---------- ---------- ---------- ---------- ----------
ADAMS            1100         10          3          5
JAMES             950         11          3          6
SMITH             800         12          3          7

14 rows selected.

 

现在，该查询已经初具雏形了。这时，对ENAME进行传统设置，并使用ORACLE字符串连接运算符“||”把它跟SAL连接起来。函数RPAD确保圆括号内的数字值整齐排列。最后，对GRP_RNK使用GROUP BY，以确保在结果集中显示每个员工（这也是使用row_number的一个目的）。