【SQL每日一练】day3：leetcode高频习题练习

题目一：订单最多的客户

题目要求：

查找下了 最多订单 的客户的 customer_number 。

测试用例生成后， 恰好有一个客户 比任何其他客户下了更多的订单。

586. 订单最多的客户 - 力扣（LeetCode）

表结构：

 运行结果示例：

思路：

        本题中，包含限定条件：恰好有一个客户比任何其他客户下了更多的订单。因此我们只需要按照customer_number字段分组排序，再使用limit子句找到最大的那个即可。

运行代码示例：

select customer_number
from Orders
group by customer_number
order by count(order_number) desc
limit 1

进阶： 如果有多位顾客订单数并列最多，如何找到所有数据？

        在原本的题目中，订单最多的客户有且仅有一个，但是如果存在订单数并列的情况，该如何使用limit子句限定查询范围呢？这种情况我们只需要使用子查询先查询出最大的订单数，再去查询订单数等于最大订单数的客户。

select customer_number
from Orders
group by customer_number
having count(order_number) = (
    select count(order_number) 
    from Orders 
    group by customer_number 
    order by count(order_number) desc 
    limit 1
)

题目二：销售员

题目要求：

编写解决方案，找出没有任何与名为 “RED” 的公司相关的订单的所有销售人员的姓名。

以 任意顺序 返回结果表。

607. 销售员 - 力扣（LeetCode）

表结构：

 运行结果示例：

思路：

        这道题目看上去比较复杂，涉及到了三张表，但是仔细读题目之后可以发现，我们只需要寻找SalesPerson表中，sales_id不属于某些部分的员工姓名，这里就用到了子查询，我们可以很容易的写出主体查询部分的框架。

select name 
from SalesPerson
where sales_id not in (
    --- 子查询部分
)

        接下来我们只需要写出子查询语句，子查询需要我们在Orders表中查询到对应的公司名为RED的com_id，我们可以使用左外连接连接两张表，并限定查询条件为name = "RED"。

        当然我们也可以不使用子查询，直接连接三表查询。

运行代码示例：

select name 
from SalesPerson
where sales_id not in (
    select sales_id 
    from Orders
    left join Company
    using(com_id)
    where name = "RED"
)

SELECT
    S.name
FROM
    salesperson S
    LEFT JOIN
    orders O ON S.sales_id = O.sales_id
    LEFT JOIN
    company C ON O.com_id = C.com_id
GROUP BY
    S.name
HAVING
    SUM(IF(C.name = 'RED', 1, 0))  = 0
ORDER BY
    S.sales_id

题目三：变更性别

题目要求：

请你编写一个解决方案来交换所有的 'f' 和 'm' （即，将所有 'f' 变为 'm' ，反之亦然），仅使用 单个 update 语句 ，且不产生中间临时表。

注意，你必须仅使用一条 update 语句，且 不能 使用 select 语句。

627. 变更性别 - 力扣（LeetCode）

表结构：

 运行结果示例：

思路：

        本题的难度在于对update语句的使用和判断操作，在MySQL中，我们可以使用update 表名 set 列 = 值 where 限定条件的语法来进行表修改操作，即：

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

        了解了update语句的语法格式，接下来的难点就是处理问题本身了，即“完成性别的互换”。实际上本题比较简单，说是“互换”，但我们只需要完成“比较”并“交换”就可以，也就是非A即B，使用case或者if都能完成。

运行代码示例：

update Salary set  sex = (case when sex = 'm' then 'f' when sex = 'f' then 'm' end)


update salary set sex=IF(sex='f','m','f')

题目四：合作过至少三次的演员和导演

题目要求：

编写解决方案找出合作过至少三次的演员和导演的 id 对 (actor_id, director_id)

1050. 合作过至少三次的演员和导演 - 力扣（LeetCode）

表结构：

 运行结果示例：

思路：

        这道题我一开始想复杂了，我也不知道为什么我一直在试图用自连接解题，浪费了不少时间。但是后来发现只要根据actor_id和director_id这两个字段分组，再用having子句统计数量筛选就好了，秒解。

运行代码示例：

select t1.actor_id,t1.director_id
from ActorDirector t1
group by t1.actor_id,t1.director_id
having count(*) >= 3

题目五：重新格式化部门表

题目要求：

重新格式化表格，使得 每个月 都有一个部门 id 列和一个收入列。

以 任意顺序 返回结果表。

1179. 重新格式化部门表 - 力扣（LeetCode）

表结构：

 运行结果示例：

思路：

        很经典的一道行列互转的题目，好像很多公司喜欢考察这类型的题目.....行列互转的题目如果是第一次遇到可能很难下手，但是只遇到过一次就会了，所有这类型的题目基本上都是一个通用解法，之前的博客有写过（等我再攒几道同类型的题出个合集~）。说回本题，总体思路依旧是遍历整表，找到符合条件的列，计算其和，之后使用列举的方式将新列作为查询结果。

        这里我们之所以要使用sum（），是因为case when只会去匹配数据库的第一个数据，如果不是它就返回空了，用sum（）就可以达到一个遍历的效果，这里换成max（）等函数都可以。

运行代码示例：

SELECT id, 
SUM(CASE WHEN month='Jan' THEN revenue END) AS Jan_Revenue,
SUM(CASE WHEN month='Feb' THEN revenue END) AS Feb_Revenue,
SUM(CASE WHEN month='Mar' THEN revenue END) AS Mar_Revenue,
SUM(CASE WHEN month='Apr' THEN revenue END) AS Apr_Revenue,
SUM(CASE WHEN month='May' THEN revenue END) AS May_Revenue,
SUM(CASE WHEN month='Jun' THEN revenue END) AS Jun_Revenue,
SUM(CASE WHEN month='Jul' THEN revenue END) AS Jul_Revenue,
SUM(CASE WHEN month='Aug' THEN revenue END) AS Aug_Revenue,
SUM(CASE WHEN month='Sep' THEN revenue END) AS Sep_Revenue,
SUM(CASE WHEN month='Oct' THEN revenue END) AS Oct_Revenue,
SUM(CASE WHEN month='Nov' THEN revenue END) AS Nov_Revenue,
SUM(CASE WHEN month='Dec' THEN revenue END) AS Dec_Revenue
FROM department
GROUP BY id
ORDER BY id;