本文共 6875 字，大约阅读时间需要 22 分钟。
解析一道笔试题目：查找各个部门工资最高的前3名员工信息
一张员工表（tab_employee）和一张部门表（tab_department）的样例数据如下所示：
SQL>SQL> select * from tab_employee; ID NAME SALARY DEPARTMENTID---------- ---------- ---------- ------------ 1 Joe 70000 1 2 Henry 80000 2 3 Sam 60000 2 4 Max 90000 1 5 Randy 85000 1已用时间: 00: 00: 00.01SQL> select * from tab_department; ID NAME---------- ---------- 1 IT 2 Sales已用时间: 00: 00: 00.00SQL>
要求编写一条查询语句，查找各个部门中工资最高的前3名员工。
解析：
创建两种数据表和准备测试数据如下：
CREATE TABLE tab_Employee
( Id NUMBER(4), Name VARCHAR2(50), Salary NUMBER(7,2), DepartmentId NUMBER(4) ); CREATE TABLE tab_Department (Id NUMBER(4), Name VARCHAR2(50) );insert into tab_Employee (Id, Name, Salary, DepartmentId) values ('1', 'Joe', '70000', '1');
insert into tab_Employee (Id, Name, Salary, DepartmentId) values ('2', 'Henry', '80000', '2'); insert into tab_Employee (Id, Name, Salary, DepartmentId) values ('3', 'Sam', '60000', '2'); insert into tab_Employee (Id, Name, Salary, DepartmentId) values ('4', 'Max', '90000', '1'); insert into tab_Employee (Id, Name, Salary, DepartmentId) values ('5', 'Randy', '85000', '1'); insert into tab_Employee (Id, Name, Salary, DepartmentId) values ('6', 'Janet', '69000', '1');insert into tab_Department (Id, Name) values ('1', 'IT');
insert into tab_Department (Id, Name) values ('2', 'Sales');commit;
解答的SQL语句如下：
SELECT a.DEPARTMENT AS Department,
a.EMPLOYEE AS Employee, a.SALARY AS Salary FROM (SELECT d.name AS DEPARTMENT, e.NAME AS EMPLOYEE, e.SALARY, ROW_NUMBER() OVER(PARTITION BY d.name ORDER BY e.Salary DESC) AS rn FROM tab_EMPLOYEE e LEFT JOIN tab_DEPARTMENT d ON e.DepartmentId = d.Id ) a WHERE rn <= 3SQL>SQL> SELECT a.DEPARTMENT AS Department, 2 a.EMPLOYEE AS Employee, 3 a.SALARY AS Salary 4 FROM 5 (SELECT d.name AS DEPARTMENT, 6 e.NAME AS EMPLOYEE, 7 e.SALARY, 8 ROW_NUMBER() OVER(PARTITION BY d.name ORDER BY e.Salary DESC) AS rn 9 FROM tab_EMPLOYEE e 10 LEFT JOIN tab_DEPARTMENT d 11 ON e.DepartmentId = d.Id 12 ) a 13 WHERE rn <= 3;DEPARTMENT EMPLOYEE SALARY---------- ---------- ----------IT Max 90000IT Randy 85000IT Joe 70000Sales Henry 80000Sales Sam 60000已用时间: 00: 00: 00.01
解答到这里，看似问题已经解决了，但是如果插入下面两行数据，结果还是这样吗?
插入两条测试数据：
SQL>SQL> insert into tab_Employee (Id, Name, Salary, DepartmentId) values ('7', 'Bean', '', '1');已创建 1 行。已用时间: 00: 00: 00.00SQL> insert into tab_Employee (Id, Name, Salary, DepartmentId) values ('8', 'Mason', '', '2');已创建 1 行。已用时间: 00: 00: 00.00SQL> commit; 即部门1和部门2中各有一名员工，工资（Salary）的值为null。
执行查询后发现结果为：
SQL>SQL> SELECT a.DEPARTMENT AS Department, 2 a.EMPLOYEE AS Employee, 3 a.SALARY AS Salary 4 FROM 5 (SELECT d.name AS DEPARTMENT, 6 e.NAME AS EMPLOYEE, 7 e.SALARY, 8 ROW_NUMBER() OVER(PARTITION BY d.name ORDER BY e.Salary DESC) AS rn 9 FROM tab_EMPLOYEE e 10 LEFT JOIN tab_DEPARTMENT d 11 ON e.DepartmentId = d.Id 12 ) a 13 WHERE rn <= 3;DEPARTMENT EMPLOYEE SALARY---------- ---------- ----------IT BeanIT Max 90000IT Randy 85000Sales MasonSales Henry 80000Sales Sam 60000已选择 6 行。已用时间: 00: 00: 00.02SQL>
我们发现查询结果已经与开始的查询要求不相符了。
说明上面的SQL语句不能通过测试！！！对于Salary为null值的情况没有进行处理。
下面的查询仍然存在问题：
SQL> SELECT b.name AS department, 2 a.name AS employee, 3 a.salary 4 FROM 5 (SELECT id, 6 name, 7 salary, 8 departmentid, 9 dense_rank() over (partition BY departmentid order by salary DESC) AS rank 10 FROM tab_employee 11 ) a 12 INNER JOIN tab_department b 13 ON a.departmentid = b.id 14 WHERE rank IN (1,2,3);DEPARTMENT EMPLOYEE SALARY---------- ---------- ----------IT BeanIT Max 90000IT Randy 85000Sales MasonSales Henry 80000Sales Sam 60000已选择 6 行。已用时间: 00: 00: 00.07
问题拓展：如果改变一下要求：查询各个部门中工资最高的员工（即工资最高的第1名）
SELECT D.name Department,
E1.Name AS Employee, E1.Salary AS Salary FROM tab_Employee E1 JOIN tab_Department D on E1.DepartmentId = D.Id AND E1.Salary >= (SELECT MAX(Salary) FROM tab_Employee E2 WHERE E1.DepartmentId = E2.DepartmentId );SQL>SQL> SELECT D.name Department, 2 E1.Name AS Employee, 3 E1.Salary AS Salary 4 FROM tab_Employee E1 5 JOIN tab_Department D 6 on E1.DepartmentId = D.Id 7 AND E1.Salary >= 8 (SELECT MAX(Salary) 9 FROM tab_Employee E2 10 WHERE E1.DepartmentId = E2.DepartmentId 11 );DEPARTMENT EMPLOYEE SALARY---------- ---------- ----------Sales Henry 80000IT Max 90000已用时间: 00: 00: 00.12SQL>
下面的查询语句：
SELECT d.Name AS Department, t.Name AS Employee, t.Salary AS SalaryFROM (SELECT Id, DepartmentId, Name, Salary, DENSE_RANK() Over (Partition BY DepartmentId order by Salary DESC) AS Rank FROM tab_Employee ) tINNER JOIN tab_Department dON t.DepartmentId = d.IdWHERE t.Rank <= 3;
SQL>SQL> SELECT d.Name AS Department, 2 t.Name AS Employee, 3 t.Salary AS Salary 4 FROM 5 (SELECT Id, 6 DepartmentId, 7 Name, 8 Salary, 9 DENSE_RANK() Over (Partition BY DepartmentId order by Salary DESC) AS Rank 10 FROM tab_Employee ) t 11 INNER JOIN tab_Department d 12 ON t.DepartmentId = d.Id 13 WHERE t.Rank <= 3;DEPARTMENT EMPLOYEE SALARY---------- ---------- ----------IT BeanIT Max 90000IT Randy 85000Sales MasonSales Henry 80000Sales Sam 60000已选择 6 行。已用时间: 00: 00: 00.10SQL>
又一种写法：
SELECT d.Name AS "Department", a.Name AS "Employee", Salary AS "Salary"FROM tab_Department dJOIN (SELECT Name, Salary, DepartmentId, DENSE_RANK() OVER (PARTITION BY DepartmentId ORDER BY Salary DESC) AS ranks FROM tab_Employee ) aON d.Id =a.DepartmentIdWHERE a.ranks<=3ORDER BY d.Name, Salary DESC, a.Name
SQL>SQL> SELECT d.Name AS "Department", 2 a.Name AS "Employee", 3 Salary AS "Salary" 4 FROM tab_Department d 5 JOIN 6 (SELECT Name, 7 Salary, 8 DepartmentId, 9 DENSE_RANK() OVER (PARTITION BY DepartmentId ORDER BY Salary DESC) AS ranks 10 FROM tab_Employee 11 ) a 12 ON d.Id =a.DepartmentId 13 WHERE a.ranks<=3 14 ORDER BY d.Name, 15 Salary DESC, 16 a.Name;Department Employee Salary---------- ---------- ----------IT BeanIT Max 90000IT Randy 85000Sales MasonSales Henry 80000Sales Sam 60000已选择 6 行。已用时间: 00: 00: 00.01
转载地址：http://kftdi.baihongyu.com/