Q607. 销售员

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
;