请用一条sql语句查询出scott.emp表中每个部门工资前三位的数据,显示结果如下:
DEPTNO SAL1 SAL2 SAL3
---------------- ---------- ------------
10 5000 2450 1300
20 3000 2975 1100
30 2850 1600 1500 则,该语句为:
SELECT
deptno,
max( sal ) sal1,
max(decode( RN, 2, sal )) sal2,
min( sal ) sal3
FROM
(
SELECT
*
FROM
( SELECT DEPTNO, SAL, ROW_NUMBER () OVER ( PARTITION BY DEPTNO ORDER BY SAL DESC ) AS RN FROM EMP )
WHERE
RN <= 3
)
GROUP BY
deptno
下面来分析一下这个题
1.首先将emp表按deptno进行分组,取出分组后的前3条数据
SELECT
*
FROM
( SELECT DEPTNO, SAL, ROW_NUMBER () OVER ( PARTITION BY DEPTNO ORDER BY SAL DESC ) AS RN FROM EMP )
WHERE
RN <= 3
row_number() over()函数,用于给数据库表中的记录进行标号,在使用的时候,其后还跟着一个函数 over(),而函数