Leetcode Database 184&185-Department (Top 3) Highest Salary-Medium (Hard)

问题1: Department Highest Salary

Problem: Write an SQL query to find employees who have the highest salary in each of the departments. Return the result table in any order.

解题思路

找到每个departments里有最高salary的employees，属于分组查询、找最值的问题

• 最直接的方法是：分组查询、分组后的条件查询salary = 组内salary最大值的employees，但会发现无法查询出department内部有多个employee有最高salary的情况，原因可能是MAX(e.salary)无法实现去重，会输出多个最大值，但加入DISTINCT关键字后，问题仍然无法解决，故而放弃该思路。

SELECT d.name Department, e.name Employee, e.salary Salary
FROM Employee e
JOIN Department d ON e.departmentId = d.id
GROUP BY e.departmentId
HAVING e.salary = MAX(e.salary);

• 官方方法：将每条记录（每个employee）的departmentId和salary结合后用于识别满足条件的employee。考点：

• 子查询：基于departmentId分组查询得到每个departmentId和其对应的最高salary

• 逻辑判断符IN的用法：可以将多个字段的值结合起来一起判断是否在某个集合内，例如此处将e.departmentId和e.salary用括号()括起来

• 一般考点：内连接INNER JOIN，两个实体 (entity) employee和department的连接，将departmentId替换成department name

Table and examples:

---

问题2: Department Top Three Salaries

Problem: A company's executives are interested in seeing who earns the most money in each of the company's departments. A high earner in a department is an employee who has a salary in the top three unique salaries for that department.

Write an SQL query to find the employees who are high earners in each of the departments.

Return the result table in any order.

官方解答思路

沿用问题1仅筛选highest salary的思路，关键问题即如何筛选出top three的所有employees

• 子查询：WHERE条件查询下是对Employee e1中的每一条记录进行条件判断，假设某一条记录有e1.departmentId和e1.salary，使用子查询筛选出Employee e2中所有和e1.departmentId相同的、且比e1.salary高的employee记录

• 筛选条件：子查询中的WHERE语句是e2.salray>e1.salary，假设e1.salary为排名第三的employee记录，那么比它高的e2.salary去重后应为2个，故count(distinct e2.salary)<=2，即<3。

SELECT
    d.Name AS 'Department', e1.Name AS 'Employee', e1.Salary
FROM
    Employee e1
        JOIN
    Department d ON e1.DepartmentId = d.Id
WHERE
    3 > (SELECT
            COUNT(DISTINCT e2.Salary)
        FROM
            Employee e2
        WHERE
            e2.Salary > e1.Salary
                AND e1.DepartmentId = e2.DepartmentId
        );

我的解答思路

和官方思路类似，但将子查询融入为自连接，更易理解。

• 在自连接时将Employee e1表格中大于等于e表格salary的数据进行匹配，基于每个employee name分组统计，筛选出符合COUNT(DISTINCT e1.salary) <= 3的name。

• 由于自连接中将e1.salary >= e.salary的e1记录都进行了匹配，故top three需要该组(employee name）下去重后的记录条数<=3，才为top three。

SELECT d.name Department, e.name Employee, e.salary Salary
FROM Employee e
    JOIN Department d ON e.departmentId = d.id
    JOIN Employee e1 ON e.departmentId = e1.departmentId AND e.salary<=e1.salary
GROUP BY e.name
HAVING COUNT(DISTINCT e1.salary) <= 3;