LeetCode上的SQL练习

182. 查找重复的电子邮箱

SELECT
    EMAIL
FROM
    PERSON
GROUP BY
    Email
HAVING
    COUNT(EMAIL) > 1;

 

181. 超过经理收入的员工

SELECT
    B. NAME AS Employee
FROM
    Employee A
INNER JOIN Employee B ON A.Id = B.ManagerId
WHERE
    A.Salary < B.Salary;

184. 部门工资最高的员工

SELECT
    D. NAME Department,
    E. NAME Employee,
    E.Salary
FROM
    -- 内连接两张查询表
    Employee E
INNER JOIN Department D ON E.DepartmentId = D.ID
WHERE
    -- 保证查询结果中E.DepartmentId, Salary对应不能最高工资
    (E.DepartmentId, Salary) IN (
        -- 分组查询出部门以及部门最高工资（有可能多个领最高工资的人,不做限定）
        SELECT
            DepartmentId,
            max(Salary)
        FROM
            Employee
        GROUP BY
            DepartmentId
    );