如何关联客户表、订单表与临时表@S，提取未涵盖全部特殊商品的客户？

需求回顾 #

现有客户表C、订单表O，以及存储特殊商品的临时表@S：

DECLARE @S TABLE (Category varchar(250), Item nvarchar(500));
INSERT INTO @S VALUES ('Hardware', 'Hammer'), ('Fruits', 'Apple')

需要筛选出未购买@S中全部特殊商品的客户，包括从未下单的客户：

• 仅购买了部分特殊商品（如只买Apple没买Hammer，或反之）的客户需保留
• 完全没有下单记录的客户也需纳入结果

示例数据 #

订单表O：

客户表C：

实现SQL #

WITH SpecialItemCounts AS (
    -- 统计特殊商品总数量
    SELECT COUNT(DISTINCT Item) AS TotalSpecialItems
    FROM @S
),
CustomerSpecialPurchases AS (
    -- 统计每个客户购买的特殊商品数量，以及对应的商品（去重）
    SELECT 
        C.CustID,
        C.Name,
        C.Forename,
        O.Item,
        COUNT(DISTINCT O.Item) OVER (PARTITION BY C.CustID) AS PurchasedSpecialCount
    FROM C
    LEFT JOIN O ON C.CustID = O.CustID
    LEFT JOIN @S S ON O.Item = S.Item
)
SELECT DISTINCT
    CustID,
    Name,
    Forename,
    Item
FROM CustomerSpecialPurchases
CROSS JOIN SpecialItemCounts
WHERE 
    -- 未购买任何特殊商品，或购买数量少于总特殊商品数
    PurchasedSpecialCount < TotalSpecialItems 
    OR PurchasedSpecialCount IS NULL
ORDER BY CustID;

逻辑说明 #

1. SpecialItemCounts：先计算@S中特殊商品的总数量（此处为2），作为判断基准。
2. CustomerSpecialPurchases：通过左连接关联客户表、订单表和特殊商品表，同时用窗口函数统计每个客户购买的不同特殊商品数量。左连接保证未下单的客户也会被包含。
3. 最终筛选：对比客户购买的特殊商品数量与总数量，只要数量不足（或为NULL，即未购买任何），就保留该客户的记录；用DISTINCT去重，避免同一客户因多订单重复出现。

预期结果 #

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