【赛迪网-it技术报道】使用分析函数进行行转列的处理: 比如查询scott.emp表的用户sal排序信息,大家可以使用下面的查询:
sql> select deptno, ename,
2 row_number () over (partition by deptno order by sal desc) seq
3 from emp;
deptno ename seq
---------- ---------- ----------
10 king 1
10 clark 2
10 miller 3
20 scott 1
20 ford 2
20 jones 3
20 adams 4
20 smith 5
30 blake 1
30 allen 2
30 turner 3
30 ward 4
30 martin 5
30 james 6
14 rows selected.
|
然后你可以结合其他函数进行行和列的转换:
sql> select deptno,
2 max(decode(seq,1,ename,null)) highest,
3 max(decode(seq,2,ename,null)) second,
4 max(decode(seq,3,ename,null)) third
5 from (
6 select deptno,ename,
7 row_number() over
8 (partition by deptno order by sal desc) seq
9 from emp)
10 where seq <=3 group by deptno
11 /
deptno highest second third
---------- ---------- ---------- ----------
10 king clark miller
20 scott ford jones
30 blake allen turner
|