mysql 取每个部门薪资最高的前3人

员工表：

部门表：

要求：取每个部门工资最高的前3人，如图：

QQ图片20180716150112.png

创建Employee(员工表)，Department(部门表)

SQL:

SELECT
	d. NAME Department,
	e1. NAME Employee,
	e1.Salary Salary
FROM
	Employee e1,
	Employee e2,
	Department d
WHERE
	e1.departmentid = e2.departmentid
AND e1.salary <= e2.salary
AND e1.departmentid = d.id
GROUP BY
	e1.id
HAVING
	count(DISTINCT e2.salary) < 3
ORDER BY
	e1.departmentid DESC
    
or

SELECT
    d. NAME dName,
    e. NAME eName,
    e.salary
FROM
    Employee e
RIGHT JOIN Department d ON e.departmentid = d.Id
WHERE
    (
        SELECT
            count(1)
        FROM
            Employee
        WHERE
            departmentid = e.departmentid
        AND salary > e.salary
    ) < 3
ORDER BY
    e.departmentid ASC,
    e.salary DESC