PL/0SQL测试题

create table TEST_SAL
(
  department    VARCHAR2(30),
  employee_name VARCHAR2(30),
  salary        NUMBER
)
/


insert into TEST_SAL (DEPARTMENT, EMPLOYEE_NAME, SALARY) values ('D1', 'E11', 5);
insert into TEST_SAL (DEPARTMENT, EMPLOYEE_NAME, SALARY) values ('D1', 'E12', 5);
insert into TEST_SAL (DEPARTMENT, EMPLOYEE_NAME, SALARY) values ('D1', 'E13', 5);
insert into TEST_SAL (DEPARTMENT, EMPLOYEE_NAME, SALARY) values ('D2', 'E21', 6);
insert into TEST_SAL (DEPARTMENT, EMPLOYEE_NAME, SALARY) values ('D2', 'E22', 5);
insert into TEST_SAL (DEPARTMENT, EMPLOYEE_NAME, SALARY) values ('D2', 'E23', 4);
insert into TEST_SAL (DEPARTMENT, EMPLOYEE_NAME, SALARY) values ('D3', 'E31', 9);
insert into TEST_SAL (DEPARTMENT, EMPLOYEE_NAME, SALARY) values ('D3', 'E32', 5);
insert into TEST_SAL (DEPARTMENT, EMPLOYEE_NAME, SALARY) values ('D3', 'E33', 4);
insert into TEST_SAL (DEPARTMENT, EMPLOYEE_NAME, SALARY) values ('D4', 'E41', 8);
insert into TEST_SAL (DEPARTMENT, EMPLOYEE_NAME, SALARY) values ('D4', 'E42', 5);
insert into TEST_SAL (DEPARTMENT, EMPLOYEE_NAME, SALARY) values ('D4', 'E43', 5);
insert into TEST_SAL (DEPARTMENT, EMPLOYEE_NAME, SALARY) values ('D4', 'E44', 2);
insert into TEST_SAL (DEPARTMENT, EMPLOYEE_NAME, SALARY) values ('D4', 'E45', 7);
insert into TEST_SAL (DEPARTMENT, EMPLOYEE_NAME, SALARY) values ('D4', 'E41', 6);

题目:
找出每个部门薪资比平均数大且薪资排在前两位的所有员工数据
分析：
    要找出每个部门的员工薪酬比较数据，所以需要按照部门名称来进行分组，然后各部门的员工再进行薪酬与平均数比较，最后取出前两名的员工数据。
题解1(使用等级函数)：

SELECT *
  FROM (SELECT t.*
              ,dense_rank() over(PARTITION BY t.department ORDER BY t.salary DESC) rank_num
          FROM test_sal t
         WHERE t.salary >= (SELECT AVG(salary) avg_sal
                              FROM test_sal t2
                             WHERE t.department = t2.department
                             GROUP BY t2.department)) r_t
 WHERE r_t.rank_num <= 2;

涉及到的知识：
    1.rank(),dense_rank()的用法：http://blog.chinaunix.net/uid-28194872-id-4108378.html
    2.group by 用法：http://blog.chinaunix.net/uid-28194872-id-4108499.html