力抠607. 销售员——SQL 相关子查询及视图

一、题目

详见：https://leetcode.cn/problems/sales-person/description/

二、分析

首先找出所有和 “RED”公司有过关联的员工，然后利用子查询和not exists来筛选没有任何与名为 “RED” 的公司相关的订单的所有销售人员的姓名。

相关子查询是外部查询逐行进行的，也就是说外部查询每进行一行，就把这行的相应值赋给子查询，并执行一次子查询（逻辑上是这样，暂不考虑sql的性能优化）。最后利用exists或not exists，in或not in来判断是否筛选该行。

非相关子查询的情况下，则先运行子查询，且只运行一次。

三、代码解读

1.select name
2.from SalesPerson
3.where not exists (
4.    select *
5.    from (
6.        select sp.name sp_name,co.name com_name
7.        from Orders od
8.        left join SalesPerson sp
9.            on od.sales_id = sp.sales_id
10.        left join Company co
11.            on od.com_id = co.com_id) a_sp_com
12.    where a_sp_com.com_name ="RED" and a_sp_com.sp_name = name
13.)

1.首先执行外部查询，1-2行，逐行查询SalesPerson表的name字段，并把某个name值存起来，将来传递给子查询。

1.select name
2.from SalesPerson

2.然后执行子查询中的子查询，6-11行，这个相当于非相关子查询，先运行且只运行一次。

6.        select sp.name sp_name,co.name com_name
7.        from Orders od
8.        left join SalesPerson sp
9.            on od.sales_id = sp.sales_id
10.        left join Company co
11.            on od.com_id = co.com_id

以订单表为基础，连接员工表和公司表，扩展员工姓名和公司名称，相当于lookupvalue，输出结果类似于：

3.执行子查询，4-12行。以第2步输出的表为基础，筛选出公司名称是“RED”，并且员工姓名是name（外部查询传递进来的值）的行。

where a_sp_com.com_name ="RED" and a_sp_com.sp_name = name

4.判断，3-13行。如果第3步输出的结果不为空，说明此时外部查询传递的name，在订单表中有对应公司名称为“RED”的样本，通过not exists把它丢弃。否则，如果第3步输出的结果为空，说明此时外部查询传递的name，在订单表中没有对应公司名称为“RED”的样本，通过not exists把它留下。

5.外部查询逐行这样循环，就得到了满足要求的结果。

四、视图

如果是实际的数据库环境，可以将子查询的子查询（飞相关子查询）定义为视图，可进一步增加可读性。以下是示例

create or replace view a_sp_com as 
select sp.name sp_name,co.name com_name
from Orders od 
left join SalesPerson sp 
    on od.sales_id = sp.sales_id
left join Company co 
    on od.com_id = co.com_id;

select name 
from SalesPerson 
where not exists (
    select * 
    from  a_sp_com
    where a_sp_com.com_name ="RED" and a_sp_com.sp_name = name
)