【SQL高频练习带刷】day13：表连接

题目一：组合两个表

题目要求：

编写解决方案，报告 Person 表中每个人的姓、名、城市和州。如果 personId 的地址不在 Address 表中，则报告为 null 。

以 任意顺序 返回结果表。

175. 组合两个表 - 力扣（LeetCode）

表结构：

运行结果示例：

思路：

        连接两张表，如果 personId 的地址不在 Address 表中，则报告为 null ，因此需要以person表作为主表进行外连接。

运行代码示例：

select firstName, lastName, city, state 
from Person
left join Address
using(personId)

题目二：没有卖出的卖家

题目要求：

写一个解决方案, 报告所有在 2020 年度没有任何卖出的卖家的名字。

返回结果按照 seller_name 升序排列。

1607. 没有卖出的卖家 - 力扣（LeetCode）

表结构：

运行结果示例：

思路：

        本题比较常规的做法就是使用子查询，查询在2020年期间没有订单信息的卖家，参考代码如下：

select seller_name
from Seller
where seller_id not in (
    select seller_id from Orders where sale_date like '2020%'
)
order by seller_name

        但是用表连接的方式也可以实现本题。我们考虑用外连接连接两张表，并且在连接时限定连接条件为订单年份在2020年期间，这样即可保留没有订单信息的用户名（其用户信息不变，连接的订单信息回显示为null），再使用where子句查询出订单信息为null的数据。

        关于on和where中的限制条件，我们可以理解为on是先对单张表进行筛选，再进行连接，主表中的数据一定会被全部保留下来，不管在连接表中是否存在对应数据。where是在表连接之后，在组成的新表中进行筛选。

运行代码示例：

select s.seller_name
from seller s 
left join orders o 
on s.seller_id=o.seller_id and year(o.sale_date) = '2020'    
where o.seller_id is null
order by s.seller_name

题目三：排名靠前的旅行者

题目要求：

编写解决方案，报告每个用户的旅行距离。

返回的结果表单，以 travelled_distance 降序排列 ，如果有两个或者更多的用户旅行了相同的距离, 那么再以 name 升序排列 。

1407. 排名靠前的旅行者 - 力扣（LeetCode）

表结构：

运行结果示例：

思路：

        本题我们正常使用左外连接连接两张表即可，需要注意需要用ifnull（）函数处理一下空值，让距离为空的数据显示为0，接下来就可以正常分组聚合，使用sum（）函数计算距离总数了，最后不要忘记排序。

运行代码示例：

select name,sum(ifnull(distance ,0)) as travelled_distance
from Users u
left join Rides r
on u.id = r.user_id
group by u.id
order by travelled_distance desc, name

题目四：销售员

题目要求：

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

以 任意顺序 返回结果表。

607. 销售员 - 力扣（LeetCode）

表结构：

运行结果示例：

思路：

        本题如果使用子查询结合in关键字非常简单，但这里依然以讲解表连接为主，提供表连接的写法。首先自然是使用外连接连接三张表，然后使用where子句筛选出公司名不为red的就可以了？但是我们观察订单表，可以发现销售员会向多家公司进行销售，但是题目要求我们找到从未与RED公司进行交易的员工姓名。因此我们可以借助分组查询来实现。我们在总表中按照员工分组，然后使用判断语句，令公司名称为RED的值为1，其他为0，计算每组数据之和，自然，如果该员工从未与RED公司交易过，那么它的和肯定是0，我们只要选出和为0的数据即可。

运行代码示例：

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

题目五：计算布尔表达式的值

题目要求：

计算表 Expressions 中的布尔表达式。

返回的结果表 无顺序要求 。

1440. 计算布尔表达式的值 - 力扣（LeetCode）

表结构：

运行结果示例：

思路：

        这道题非常有意思，强烈建议自己思考一下。当然我们这道题目也可以暴力破解，使用case when + union完成。但是，也可以用表连接优雅的完成。我们将Expressions作为主表，分别将左、右操作数作为条件连接变量表两次，得到如下结果：

在该表的基础上，我们再进行判断就可以了。

运行代码示例：

select e.*,
(
    case
        when operator = '=' and v1.value = v2.value then 'true'
        when operator = '>' and v1.value > v2.value then 'true'
        when operator = '<' and v1.value < v2.value then 'true'
        else 'false'
    end
) value
from Expressions e 
left join Variables v1 
on e.left_operand = v1.name 
left join Variables v2
on e.right_operand = v2.name