如何查询未关联CustTypeKey=1客户的DesignGroup

数据表结构 #

DesignGroup表 #

+--------------------------------------+----------+
|            DesignGroupId             |   Name   |
+--------------------------------------+----------+
| 3A81C1FF-442F-4291-B8E2-7079D80920CF | Design 1 |
| 3238F4C6-7BA7-4B3F-9383-17702B0D1CC3 | Design 2 |
+--------------------------------------+----------+

DesignGroupCustomers表 #

+--------------------------------------+--------------------------------------+-------------+
|        DesignGroupCustomerId         |          DesignGroupId (FK)          | CustomerKey |
+--------------------------------------+--------------------------------------+-------------+
| D0828677-F295-46F7-BB85-65888D5A48B7 | 3A81C1FF-442F-4291-B8E2-7079D80920CF |          10 |
| 10C01BB9-1DDB-4DB4-BEC4-9539E030BF68 | 3A81C1FF-442F-4291-B8E2-7079D80920CF |          20 |
| F88C9F66-C0D9-EB11-8481-5CF9DDF6DC87 | 3238F4C6-7BA7-4B3F-9383-17702B0D1CC3 |          10 |
+--------------------------------------+--------------------------------------+-------------+

CustomerTable表 #

+-------------+-------------+
| CustomerKey | CustTypeKey |
+-------------+-------------+
|          10 |           2 |
|          20 |           1 |
+-------------+-------------+

需求 #

仅返回未关联CustTypeKey=1客户的DesignGroup，本场景预期返回Design 2。

你尝试的CTE代码 #

;WITH CTE
    AS (SELECT
             [DG].[DesignGroupId]
            , ROW_NUMBER() OVER(PARTITION BY [DesignGroupCustomer]) AS [RN]
             FROM [DesignGroup] AS [DG]
                 INNER JOIN [DesignGroupCustomer] AS [DGC] ON [DG].[DesignGroupId] = [DGC].[DesignGroupId]
                 INNER JOIN [Customer] AS [C] ON [DGC].[CustomerKey] = [C].[CustomerKey]
                 INNER JOIN [CustomerType] AS [CT] ON [C].[CustTypeKey] = [CT].[CustTypeKey])
    SELECT
          [DesignGroupId]
          FROM [CTE] -- WHERE CustomerType NOT CONTAINS (1)

正确的CTE实现方案 #

;WITH DesignGroupCustTypeStats AS (
    SELECT
        DG.DesignGroupId,
        DG.Name,
        -- 标记该分组是否存在CustTypeKey=1的客户
        MAX(CASE WHEN C.CustTypeKey = 1 THEN 1 ELSE 0 END) AS HasCustType1
    FROM DesignGroup DG
    LEFT JOIN DesignGroupCustomers DGC 
        ON DG.DesignGroupId = DGC.DesignGroupId
    LEFT JOIN CustomerTable C 
        ON DGC.CustomerKey = C.CustomerKey
    GROUP BY DG.DesignGroupId, DG.Name
)
SELECT DesignGroupId, Name
FROM DesignGroupCustTypeStats
WHERE HasCustType1 = 0;

另一种更简洁的实现（NOT EXISTS） #

如果不需要强制用CTE，NOT EXISTS的写法逻辑更直观：

SELECT DG.DesignGroupId, DG.Name
FROM DesignGroup DG
WHERE NOT EXISTS (
    SELECT 1
    FROM DesignGroupCustomers DGC
    JOIN CustomerTable C 
        ON DGC.CustomerKey = C.CustomerKey
    WHERE DGC.DesignGroupId = DG.DesignGroupId
      AND C.CustTypeKey = 1
);

方案说明 #

1. CTE方案：通过分组统计每个DesignGroup的客户类型情况，MAX(CASE...)会在该分组存在CustTypeKey=1的客户时返回1，否则返回0，最后筛选出标记为0的分组即可。
2. NOT EXISTS方案：直接检查当前DesignGroup是否没有关联到CustTypeKey=1的客户记录，数据库会高效执行这种存在性检查，性能通常不错。

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