我有一個 DesignGroup 表：
-------------------------------------- ----------
| DesignGroupId | Name |
-------------------------------------- ----------
| 3A81C1FF-442F-4291-B8E2-7079D80920CF | Design 1 |
| 3238F4C6-7BA7-4B3F-9383-17702B0D1CC3 | Design 2 |
-------------------------------------- ----------
每個 DesignGroup 可以有多個客戶，所以我有一個表 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 |
-------------------------------------- -------------------------------------- -------------
每個客戶都有一個 CustomerType 作為 customerTable：
------------- -------------
| CustomerKey | CustTypeKey |
------------- -------------
| 10 | 2 |
| 20 | 1 |
------------- -------------
我想要實作的是只得到這個宣告：
僅回傳沒有 custTypeKey = 1 客戶的 DesignGroup
在這種情況下，它應該回傳 Design 2，因為它沒有 custTypeKey = 1 的客戶
我在考慮 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)
uj5u.com熱心網友回復：
WITH temp AS (
SELECT DISTINCT
dgc.DesignGroupId AS DesignGroupId
FROM DesignGroupCustomers dgc
INNER JOIN customerTable ct
ON dgc.CustomerKey = ct.CustomerKey
WHERE ct.CustTypeKey = 1
)
SELECT
DesignGroupId
FROM DesignGroup
WHERE DesignGroupId NOT IN (
SELECT
DesignGroupId
FROM temp
)
首先，您可以獲取所有具有 CustTypeKey =1 的設計組，然后使用NOT IN獲取所有其他設計組。如果您遇到任何問題，請告訴我
uj5u.com熱心網友回復：
您可以使用子查詢回傳此客戶型別鍵為 1 的設計組，然后回傳LEFT JOIN設計表上的子查詢并過濾到具有DesignGroupIdnull 的結果（任何未包含在資料集中的設計組）子查詢）
SELECT d.[DesignGroupId]
FROM [DesignGroup] AS d
LEFT JOIN
(
SELECT dgc.[DesignGroupId]
FROM [DesignGroupCustomer] AS dgc
ON dgc.[DesignGroupId] = d.[DesignGroupId]
INNER JOIN [Customer] AS c
ON c.[CustomerKey] = dgc.[CustomerKey]
WHERE c.[CustTypeKey] = 1
GROUP BY dgc.[DesignGroupId]
) x
ON x.[DesignGroupId] = d.[DesignGroupId]
WHERE x.[DesignGroupId] IS NULL
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/514574.html