MySQL之各部门前3高工资的员工

创建如下图所示的employee表格

在创建Department表格

问题是找出每个部门工资前三高的员工。
问题思路：
大致就是通过两个employee的表，进行对比，约束条件是 departmentid 相等并且salary 最大的。
而小于三则是对数量的约束
完整的SQL语句为

CREATE TABLE IF NOT EXISTS Employee(
		Id INT PRIMARY KEY,
		Name varchar(10) NOT NULL,
		Salary FLOAT NOT NULL,
		DepartmentId INT NOT NULL
)Engine=Innodb;
INSERT INTO Employee VALUES(1,'Joe',70000,1);
INSERT INTO Employee VALUES(2,'Henry',80000,2);
INSERT INTO Employee VALUES(3,'Sam',60000,2);
INSERT INTO Employee VALUES(4,'Max',90000,1);
INSERT INTO Employee VALUES(5,'Janet',69000,1);
INSERT INTO Employee VALUES(6,'Randy',85000,1);

CREATE TABLE IF NOT EXISTS Department(
		Id INT PRIMARY KEY,
		Name varchar(10) NOT NULL
)Engine=Innodb;
INSERT INTO Department VALUES(1,'IT');
INSERT INTO Department VALUES(2,'Sales');

SELECT D1.Name Department, E1.Name Employee,  E1.Salary  
FROM Employee E1, Employee E2, Department D1  
WHERE E1.DepartmentID = E2.DepartmentID  
AND E2.Salary >= E1.Salary   
AND E1.DepartmentID = D1.ID        
GROUP BY E1.Name  
HAVING COUNT(DISTINCT E2.Salary) <= 3  
ORDER BY D1.Name, E1.Salary DESC;   

结果为

问题分析的方式不止一种，求解的方法也不止一种，欢迎更好地结果，请留言。