Q607. 销售员
描述
给定 3 个表: salesperson
, company
, orders
。
输出所有表 salesperson
中,没有向公司 'RED'
销售任何东西的销售员。
示例:
输入
表: salesperson
+----------+------+--------+-----------------+-----------+
| sales_id | name | salary | commission_rate | hire_date |
+----------+------+--------+-----------------+-----------+
| 1 | John | 100000 | 6 | 4/1/2006 |
| 2 | Amy | 120000 | 5 | 5/1/2010 |
| 3 | Mark | 65000 | 12 | 12/25/2008|
| 4 | Pam | 25000 | 25 | 1/1/2005 |
| 5 | Alex | 50000 | 10 | 2/3/2007 |
+----------+------+--------+-----------------+-----------+
表 salesperson
存储了所有销售员的信息。每个销售员都有一个销售员编号 sales_id
和他的名字 name
。
表: company
+---------+--------+------------+
| com_id | name | city |
+---------+--------+------------+
| 1 | RED | Boston |
| 2 | ORANGE | New York |
| 3 | YELLOW | Boston |
| 4 | GREEN | Austin |
+---------+--------+------------+
表 company
存储了所有公司的信息。每个公司都有一个公司编号 com_id
和它的名字 name
。
表: orders
+----------+------------+---------+----------+--------+
| order_id | order_date | com_id | sales_id | amount |
+----------+------------+---------+----------+--------+
| 1 | 1/1/2014 | 3 | 4 | 100000 |
| 2 | 2/1/2014 | 4 | 5 | 5000 |
| 3 | 3/1/2014 | 1 | 1 | 50000 |
| 4 | 4/1/2014 | 1 | 4 | 25000 |
+----------+----------+---------+----------+--------+
表 orders
存储了所有的销售数据,包括销售员编号 sales_id
和公司编号 com_id
。
输出
+------+
| name |
+------+
| Amy |
| Mark |
| Alex |
+------+
解释
根据表 orders
中的订单 '3'
和 '4'
,容易看出只有 'John'
和 'Pam'
两个销售员曾经向公司 'RED'
销售过。
所以我们需要输出表 salesperson
中所有其他人的名字。
解法一:疯狂子查询
思路
大象放冰箱要几步?三步,打开冰箱,放入大象,合上冰箱。
此题同理
第一步,查到 RED 编号
select com_id
from company
where name = 'RED'
;
第二步,查到卖给 RED 的订单
select distinct sales_id
from orders
where com_id = (
select com_id
from company
where name = 'RED'
)
;
第三步,查到销售人员的名字,因为要查的是没卖给 RED 的,所以要 not 一下
select name
from salesperson A
where A.sales_id not in ( # not 一下
select distinct sales_id
from orders
where com_id = (
select com_id
from company
where name = 'RED'
)
)
;
SQL
select name
from salesperson A
where A.sales_id not in (
select distinct sales_id
from orders
where com_id = (
select com_id
from company
where name = 'RED'
)
)
;
解法二:连接 + 子查询
思路
解法一的第一步和第二步可以合并一步,直接通过 com_id
将 company
与 orders
连接在一起,筛选出卖给 RED 的订单的销售人员编号。
select distinct sales_id
from orders A
left join company B
on A.com_id = B.com_id
where name = 'RED'
;
剩下不变,还是根据销售人员编号查销售人员人民。
select name
from salesperson
where sales_id not in ( # not 一下
select distinct sales_id
from orders A
left join company B
on A.com_id = B.com_id
where name = 'RED'
)
;
SQL
select name
from salesperson
where sales_id not in (
select distinct sales_id
from orders A
left join company B
on A.com_id = B.com_id
where name = 'RED'
)
;
解法三:只用连接
思路
通过 sales_id
和 com_id
将三张表连在一起。按照销售人员名字分组后再做筛选
SQL
select A.name
from salesperson A
left join orders B on A.sales_id = B.sales_id
left join company C on B.com_id = C.com_id
group by A.name
having sum(if(C.name = 'RED', 1, 0)) = 0
;