在emp表中数据如下ENAME SAL
------------------------------ ----------
SMITH 800
ALLEN 1600
WARD 1250
JONES 2975
MARTIN 1250
BLAKE 2850
CLARK 2450
SCOTT 3000
KING 5000
TURNER 1500
ADAMS 1100
JAMES 950
FORD 3000
MILLER 1300
要求将工资前3名为1级工资,次3名为2级工资,剩余的为3级工资,分别显示为一列
最后显示后的结果应为--------------- --------------- ---------------
KING(5000) BLAKE(2850) TURNER(1500)
FORD(3000) CLARK(2450) MILLER(1300)
SCOTT(3000) ALLEN(1600) WARD(1250)
JONES(2975) MARTIN(1250)
ADAMS(1100)
JAMES(950)
SMITH(800)
sql语句如下SELECT MAX(CASE
WHEN grp = 1 THEN
ename || '(' || sal || ')'
END) AS lv1,
MAX(CASE
WHEN grp = 2 THEN
ename || '(' || sal || ')'
END) AS lv2,
MAX(CASE
WHEN grp = 3 THEN
ename || '(' || sal || ')'
END) AS lv3
FROM (SELECT ename,
sal,
/* 生成分组依据*/
CASE
WHEN rn <= 3 THEN
1
WHEN rn <= 6 THEN
2
ELSE
3
END AS grp,
/*生成分组的序列*/
row_number() over(PARTITION BY(CASE
WHEN rn <= 3 THEN
1
WHEN rn <= 6 THEN
2
ELSE
3
END) ORDER BY sal DESC) AS rn
FROM (SELECT ename, sal,
/* 使用dense_rank生成序列号,并列的重复显示 */
dense_rank() over(ORDER BY sal DESC) rn
FROM emp))
GROUP BY rn
ORDER BY rn
/
SQL> col lv1 for a15;
SQL> col lv2 for a15;
SQL> col lv3 for a15;
SQL> /
LV1 LV2 LV3
--------------- --------------- ---------------
KING(5000) BLAKE(2850) TURNER(1500)
FORD(3000) CLARK(2450) MILLER(1300)
SCOTT(3000) ALLEN(1600) WARD(1250)
JONES(2975) MARTIN(1250)
ADAMS(1100)
JAMES(950)
SMITH(800)
7 rows selected.