Employee 表包含所有員工信息测萎，每個(gè)員工有其對(duì)應(yīng)的 Id, salary 和 department Id 。
+----+-------+--------+--------------+
| Id | Name | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1 | Joe | 70000 | 1 |
| 2 | Henry | 80000 | 2 |
| 3 | Sam | 60000 | 2 |
| 4 | Max | 90000 | 1 |
| 5 | Janet | 69000 | 1 |
| 6 | Randy | 85000 | 1 |
+----+-------+--------+--------------+
Department 表包含公司所有部門的信息感混。
+----+----------+
| Id | Name |
+----+----------+
| 1 | IT |
| 2 | Sales |
+----+----------+
編寫一個(gè) SQL 查詢植锉，找出每個(gè)部門工資前三高的員工。例如幸斥，根據(jù)上述給定的表格匹摇，查詢結(jié)果應(yīng)返回：
+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT | Max | 90000 |
| IT | Randy | 85000 |
| IT | Joe | 70000 |
| Sales | Henry | 80000 |
| Sales | Sam | 60000 |
+------------+----------+--------+
答案：
select b.name Department, a.Name Employee, a.Salary Salary
from Employee a inner join Department b
on a.DepartmentId = b.Id
where (select count(distinct salary)
from Employee
where salary > a.salary
and DepartmentId= b.id ) < 3
order by a.DepartmentId,a.Salary desc ;
