SQL力扣练习（五）-CSDN博客

本文链接：https://blog.csdn.net/weixin_53011574/article/details/131577562

1.从不订购的客户(183)

某网站包含两个表，Customers 表和 Orders 表。编写一个 SQL 查询，找出所有从不订购任何东西的客户。

Customers 表：

+----+-------+
| Id | Name  |
+----+-------+
| 1  | Joe   |
| 2  | Henry |
| 3  | Sam   |
| 4  | Max   |
+----+-------+

Orders 表：

+----+------------+
| Id | CustomerId |
+----+------------+
| 1  | 3          |
| 2  | 1          |
+----+------------+

例如给定上述表格，你的查询应返回：

+-----------+
| Customers |
+-----------+
| Henry     |
| Max       |
+-----------+

解法一(not in)

# Write your MySQL query statement below distinct name Customers 
select customers.name as 'Customers'
from customers
where customers.id not in
(
    select customerid from orders
);

解法二(is)

# Write your MySQL query statement below distinct name Customers 
select c.Name as Customers 
from Customers as c
left join Orders as o on c.Id = o.CustomerId
where o.Id is null

解法三(not exists)

select name as Customers
  from Customers c
 where not exists (select 1 from Orders o where c.id = o.CustomerId)

解法四(isnull函数)

select name Customers from customers left join orders
on customers.id = orders.customerId where isnull(customerId);

2.部门工资最高的员工（184）

表： Employee

+--------------+---------+
| 列名          | 类型    |
+--------------+---------+
| id           | int     |
| name         | varchar |
| salary       | int     |
| departmentId | int     |
+--------------+---------+
id是此表的主键列。
departmentId是Department表中ID的外键。
此表的每一行都表示员工的ID、姓名和工资。它还包含他们所在部门的ID。

表： Department

+-------------+---------+
| 列名         | 类型    |
+-------------+---------+
| id          | int     |
| name        | varchar |
+-------------+---------+
id是此表的主键列。
此表的每一行都表示一个部门的ID及其名称。
编写SQL查询以查找每个部门中薪资最高的员工。
按 任意顺序 返回结果表。

解法一（in）

# Write your MySQL query statement below
select d.name Department,e.name Employee,e.salary Salary 
from employee e join department d 
on e.departmentId=d.id 
where (e.departmentId,salary) 
in (select departmentId,max(salary) from employee group by departmentId)

解法二（rank窗口函数）

select Department, Employee, Salary
from 
(
select 
    D.Name as Department, 
    E.Name as Employee, 
    E.Salary as Salary, 
    rank() over(partition by D.Name order by E.Salary desc) as rank_
from Employee E join Department D on E.DepartmentId = D.Id
) as tmp
where rank_ = 1