LeetCode-数据库184-185

184题干：

编写SQL查询以查找每个部门中薪资最高的员工。
按 任意顺序 返回结果表。

SQL架构：

Create table If Not Exists Employee (id int, name varchar(255), salary int, departmentId int)

Create table If Not Exists Department (id int, name varchar(255))

Truncate table Employee

insert into Employee (id, name, salary, departmentId) values ('1', 'Joe', '70000', '1')

insert into Employee (id, name, salary, departmentId) values ('2', 'Jim', '90000', '1')

insert into Employee (id, name, salary, departmentId) values ('3', 'Henry', '80000', '2')

insert into Employee (id, name, salary, departmentId) values ('4', 'Sam', '60000', '2')

insert into Employee (id, name, salary, departmentId) values ('5', 'Max', '90000', '1')

Truncate table Department

insert into Department (id, name) values ('1', 'IT')

insert into Department (id, name) values ('2', 'Sales')

示例：

 

 

 我的题解

SELECT D.name AS 'Department',
E.name AS 'Employee',
E.salary AS 'Salary'
FROM Employee E LEFT JOIN Department D ON
E.departmentId = D.id
WHERE (E.departmentId,E.salary) IN 
(SELECT departmentId, MAX(salary)
FROM Employee 
GROUP BY departmentId)

优秀题解

# Write your MySQL query statement below
#对于不同的部门，我们要分别找，其中工资最高的员工，然后不要去重
#先找到一个部门内工作最高的，它的部门和工资数目
# SELECT
#     DepartmentId, MAX(Salary)
# FROM
#     Employee
# GROUP BY DepartmentId;

#然后把表 Employee 和 Department 连接，再在这张临时表里用 IN 语句查询部门名字和工资的关系。

select
    Department.name as 'Department',
    Employee.name as 'Employee',
    salary as Salary
FROM
    Employee JOIN Department
    ON Employee.departmentid = Department.id #拼接条件
WHERE       #从这里开始设置查询条件
    (Employee.departmentid,salary) IN
    (
        select Employee.departmentid, max(salary)
        FROM
            Employee
        GROUP BY DepartmentId
    );

---

185题干：

公司的主管们感兴趣的是公司每个部门中谁赚的钱最多。一个部门的 高收入者 是指一个员工的工资在该部门的 不同 工资中 排名前三 。

编写一个SQL查询，找出每个部门中 收入高的员工 。

以 任意顺序 返回结果表。

SQL架构

Create table If Not Exists Employee (id int, name varchar(255), salary int, departmentId int)
Create table If Not Exists Department (id int, name varchar(255))
Truncate table Employee
insert into Employee (id, name, salary, departmentId) values ('1', 'Joe', '85000', '1')
insert into Employee (id, name, salary, departmentId) values ('2', 'Henry', '80000', '2')
insert into Employee (id, name, salary, departmentId) values ('3', 'Sam', '60000', '2')
insert into Employee (id, name, salary, departmentId) values ('4', 'Max', '90000', '1')
insert into Employee (id, name, salary, departmentId) values ('5', 'Janet', '69000', '1')
insert into Employee (id, name, salary, departmentId) values ('6', 'Randy', '85000', '1')
insert into Employee (id, name, salary, departmentId) values ('7', 'Will', '70000', '1')
Truncate table Department
insert into Department (id, name) values ('1', 'IT')
insert into Department (id, name) values ('2', 'Sales')

 

示例

 

 

 我的题解

# Write your MySQL query statement below
SELECT D.name AS 'Department',
E.name AS 'Employee',
E.salary AS 'Salary'
FROM Employee E LEFT JOIN Department D ON
E.departmentId = D.id
WHERE (E.departmentId,E.salary) IN
(SELECT P.departmentId,P.salary
FROM(SELECT departmentId, name,salary,
DENSE_RANK() OVER (PARTITION BY departmentId ORDER BY salary DESC) AS 'RANK'
FROM Employee) P
WHERE P.RANK <= 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
        )
;

作者：LeetCode
链接：https://leetcode-cn.com/problems/department-top-three-salaries/solution/bu-men-gong-zi-qian-san-gao-de-yuan-gong-by-leetco/
来源：力扣（LeetCode）
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。

使用窗口函数

# Write your MySQL query statement below
select d.name Department ,t.name Employee ,t.salary Salary 
from
    (
        select name,
                salary,
                dense_rank() over(partition by departmentId order by Salary desc) rk,
                departmentId 
        from  Employee 
    )t
join Department d on t.departmentId =d.id
where t.rk<=3

 

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/department-top-three-salaries
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。