在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.
