题目一:组合两个表
题目要求:
编写解决方案,报告
Person
表中每个人的姓、名、城市和州。如果personId
的地址不在Address
表中,则报告为null
。以 任意顺序 返回结果表。
表结构:
运行结果示例:
思路:
连接两张表,如果
personId
的地址不在Address
表中,则报告为null
,因此需要以person表作为主表进行外连接。
运行代码示例:
select firstName, lastName, city, state
from Person
left join Address
using(personId)
题目二:没有卖出的卖家
题目要求:
写一个解决方案, 报告所有在
2020
年度没有任何卖出的卖家的名字。返回结果按照
seller_name
升序排列。
表结构:
运行结果示例:
思路:
本题比较常规的做法就是使用子查询,查询在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
升序排列 。
表结构:
运行结果示例:
思路:
本题我们正常使用左外连接连接两张表即可,需要注意需要用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” 的公司相关的订单的所有销售人员的姓名。
以 任意顺序 返回结果表。
表结构:
运行结果示例:
思路:
本题如果使用子查询结合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
中的布尔表达式。返回的结果表 无顺序要求 。
表结构:
运行结果示例:
思路:
这道题非常有意思,强烈建议自己思考一下。当然我们这道题目也可以暴力破解,使用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