如何完善用于WHERE子句过滤的CTE查询，完成关联与逻辑？

原SQL通过WHERE子句子查询实现了排除特定客户的需求，运行正常。现希望改用CTE优化代码结构，但不清楚如何完成CTE与主查询的关联及最终过滤逻辑。

原SQL代码 #

select 
    cus.FirstName as First_Name,
    cus.LastName as Last_Name,
    cus.customerid as Customer_ID,
    prod.name as Product_Name, 
    prod.ProductID as Product_ID,
    prod.ProductCategoryID as Product_Category_ID
from saleslt.SalesOrderHeader hd  
left join saleslt.SalesOrderDetail sod ON hd.SalesOrderID = sod.SalesOrderID
left join saleslt.customer cus ON cus.CustomerID = hd.CustomerID
left join saleslt.product prod ON prod.ProductID = sod.ProductID
where cus.customerid NOT IN (select 
                                min(cus.customerid) as Customer_ID
                            from saleslt.SalesOrderHeader hd  
                            left join saleslt.SalesOrderDetail sod ON hd.SalesOrderID = sod.SalesOrderID
                            left join saleslt.customer cus ON cus.CustomerID = hd.CustomerID
                            left join saleslt.product prod ON prod.ProductID = sod.ProductID
                            where prod.productcategoryid in ('5','6','7')
                            group by cus.customerid
                            )
ORDER BY cus.FirstName, cus.LastName

未完成的CTE代码 #

;WITH test as (
select min(cus.customerid) as Customer_ID
from saleslt.SalesOrderHeader hd  
left join saleslt.SalesOrderDetail sod ON hd.SalesOrderID = 
sod.SalesOrderID
left join saleslt.customer cus ON cus.CustomerID = hd.CustomerID
left join saleslt.product prod ON prod.ProductID = sod.ProductID
where prod.productcategoryid in ('5','6','7')
group by cus.customerid
)
select 
    cus.FirstName as First_Name,
    cus.LastName as Last_Name,
    cus.customerid as Customer_ID,
    prod.name as Product_Name, 
    prod.ProductID as Product_ID,
    prod.ProductCategoryID as Product_Category_ID
from saleslt.SalesOrderHeader hd  
left join saleslt.SalesOrderDetail sod ON hd.SalesOrderID = sod.SalesOrderID
left join saleslt.customer cus ON cus.CustomerID = hd.CustomerID
left join saleslt.product prod ON prod.ProductID = sod.ProductID
left join test t ON t.??? = ???
where cus.customerid NOT IN test.customerID

解决方案 #

核心优化点 #

1. 简化CTE：由于CTE中按cus.customerid分组，min(cus.customerid)等价于直接取cus.customerid，无需使用聚合函数。
2. 无需关联CTE：CTE的作用是存储需要排除的客户ID列表，主查询直接通过过滤条件引用即可，不需要额外关联操作。

方式1：使用NOT IN（与原逻辑一致） #

;WITH excluded_customers as (
    select cus.customerid as Customer_ID
    from saleslt.SalesOrderHeader hd  
    left join saleslt.SalesOrderDetail sod ON hd.SalesOrderID = sod.SalesOrderID
    left join saleslt.customer cus ON cus.CustomerID = hd.CustomerID
    left join saleslt.product prod ON prod.ProductID = sod.ProductID
    where prod.productcategoryid in ('5','6','7')
    group by cus.customerid
)
select 
    cus.FirstName as First_Name,
    cus.LastName as Last_Name,
    cus.customerid as Customer_ID,
    prod.name as Product_Name, 
    prod.ProductID as Product_ID,
    prod.ProductCategoryID as Product_Category_ID
from saleslt.SalesOrderHeader hd  
left join saleslt.SalesOrderDetail sod ON hd.SalesOrderID = sod.SalesOrderID
left join saleslt.customer cus ON cus.CustomerID = hd.CustomerID
left join saleslt.product prod ON prod.ProductID = sod.ProductID
where cus.customerid NOT IN (select Customer_ID from excluded_customers)
ORDER BY cus.FirstName, cus.LastName

方式2：使用NOT EXISTS（更安全，避免NULL值问题） #

如果CTE中可能出现Customer_ID为NULL的情况，NOT IN会导致结果异常，推荐使用NOT EXISTS：

;WITH excluded_customers as (
    select cus.customerid as Customer_ID
    from saleslt.SalesOrderHeader hd  
    left join saleslt.SalesOrderDetail sod ON hd.SalesOrderID = sod.SalesOrderID
    left join saleslt.customer cus ON cus.CustomerID = hd.CustomerID
    left join saleslt.product prod ON prod.ProductID = sod.ProductID
    where prod.productcategoryid in ('5','6','7')
    group by cus.customerid
)
select 
    cus.FirstName as First_Name,
    cus.LastName as Last_Name,
    cus.customerid as Customer_ID,
    prod.name as Product_Name, 
    prod.ProductID as Product_ID,
    prod.ProductCategoryID as Product_Category_ID
from saleslt.SalesOrderHeader hd  
left join saleslt.SalesOrderDetail sod ON hd.SalesOrderID = sod.SalesOrderID
left join saleslt.customer cus ON cus.CustomerID = hd.CustomerID
left join saleslt.product prod ON prod.ProductID = sod.ProductID
where NOT EXISTS (
    select 1 
    from excluded_customers ec 
    where ec.Customer_ID = cus.customerid
)
ORDER BY cus.FirstName, cus.LastName

补充说明 #

• 给CTE命名为excluded_customers比test更具语义性，提升代码可读性。
• 主查询中不需要关联CTE，因为我们只需要用CTE中的客户ID列表做过滤，关联会增加不必要的计算开销。

内容的提问来源于stack exchange，提问作者Meditation