要求如下,给表中的值分等级,然后把结果集转置为三列,其思想是将最高的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的一个目的)。