PL/SQL练习题三(分析函数)

使用Oracle提供的sample数据。

查询每个员工在自己部门中的工资排名

SELECT empno,ename,COUNT(*) OVER (PARTITION BY deptno ORDER BY sal)FROM emp;

或

select deptno,empno,ename,  sal,rank() over (partition by deptno order by sal desc) as rank_valuefrom emp

Display the 2nd highest paid employee in each department.

SELECT dep_id,emp_idFROM (SELECT dep_id,RANK() OVER (PARTITION BY dep_id ORDER BY salary DESC) rank_num,emp_idFROM employee)WHERE rank_num <= 2

注意这里不要想去掉子查询。rank()和开窗函数好像不能一起使用。