假設我有兩個表：IndustryCustomers和ProductCustomers，它們具有相同的架構和只有一個這樣的列
行業客戶：
| 客戶ID |
|---|
| 1 |
| 2 |
| 3 |
產品客戶：
| 客戶ID |
|---|
| 2 |
| 3 |
| 4 |
所以我想要的是：
1-如果行業客戶和產品客戶都有記錄，那么在他們之間獲得共同客戶（只需通過客戶ID的內部連接）
2-如果行業客戶有任何記錄但產品客戶沒有記錄然后選擇所有行業客戶
3- 如果行業客戶沒有任何記錄，則選擇所有產品客戶
目前我是通過使用IF并根據條件選擇來做到這一點的，但我想知道我是否可以通過一個查詢來獲得客戶。
這是我的查詢
IF EXISTS (SELECT TOP 1 1 FROM #IndustryCustomers)
BEGIN
IF EXISTS (SELECT TOP 1 1 FROM #ProductCustomers)
SELECT *
FROM #IndustryCustomers ic
JOIN #ProductCustomers pc
ON ic.CustomerId = pc.CustomerId;
ELSE
SELECT *
FROM #IndustryCustomers;
END;
ELSE
SELECT *
FROM #ProductCustomers;
uj5u.com熱心網友回復：
你可以UNION ALL你的三個 SELECT 并將相應的條件放在 WHERE 子句中，例如
SELECT ic.CustomerId
FROM #IndustryCustomers AS ic
INNER JOIN #ProductCustomers AS pc ON ic.CustomerId = pc.CustomerId
WHERE EXISTS (SELECT 1 FROM #IndustryCustomers)
AND EXISTS (SELECT 1 FROM #ProductCustomers)
UNION ALL
SELECT ic.CustomerId
FROM #IndustryCustomers AS ic
WHERE EXISTS (SELECT 1 FROM #IndustryCustomers)
AND NOT EXISTS (SELECT 1 FROM #ProductCustomers)
UNION ALL
SELECT pc.CustomerId
FROM #ProductCustomers AS pc
WHERE NOT EXISTS (SELECT 1 FROM #IndustryCustomers)
顯然，這要求所有三個 SQL 都回傳相同的列集，所以我簡化*為客戶 ID。
不過，我確實認為，這個“解決方案”雖然正式滿足您的要求，但比您當前的解決方案可讀性差……
uj5u.com熱心網友回復：
TLDR；
SELECT
*
FROM (
SELECT
CustomerID = CASE
WHEN NOT EXISTS (SELECT * FROM ProductCustomers) THEN
ic.CustomerID
WHEN NOT EXISTS (SELECT * FROM IndustryCustomers) THEN
pc.CustomerID
WHEN EXISTS (SELECT * FROM IndustryCustomers)
AND EXISTS (SELECT * FROM ProductCustomers)
AND ic.CustomerID = pc.CustomerID THEN
ic.CustomerID
END
FROM IndustryCustomers AS ic
FULL JOIN ProductCustomers AS pc
ON pc.CustomerID = ic.CustomerID
) AS x
WHERE x.CustomerID IS NOT NULL;
查詢的細分
第一步：獲取所有資料
如果您想要一個查詢，但不想使用 a UNION，則需要執行FULL JOIN兩個表中的 a ：
SELECT
*
FROM IndustryCustomers AS ic
FULL JOIN ProductCustomers AS pc
ON pc.CustomerID = ic.CustomerID;
| ic.CustomerID | pc.CustomerID |
|---|---|
| 2 | 2 |
| 3 | 3 |
| 空值 | 4 |
| 1 | 空值 |
第 2 步：根據您的邏輯過濾選擇串列中的資料
現在，您擁有了產生所需結果所需的所有資料。現在更改結果中的列以根據您的邏輯回傳所需的結果。如果沒有ProductCustomers，總是回傳IndustryCustomers，如果不IndustryCustomers總是回傳ProductCustomers，并且如果兩者都有記錄，則只回傳匹配的記錄。
SELECT
CustomerID = CASE
WHEN NOT EXISTS (SELECT * FROM ProductCustomers) THEN
ic.CustomerID
WHEN NOT EXISTS (SELECT * FROM IndustryCustomers) THEN
pc.CustomerID
WHEN EXISTS (SELECT * FROM IndustryCustomers)
AND EXISTS (SELECT * FROM ProductCustomers)
AND ic.CustomerID = pc.CustomerID THEN
ic.CustomerID
END
FROM IndustryCustomers AS ic
FULL JOIN ProductCustomers AS pc
ON pc.CustomerID = ic.CustomerID;
| 客戶ID |
|---|
| 2 |
| 3 |
| 空值 |
| 空值 |
第 3 步：通過洗掉 NULLS 來清理結果
這為您提供了您想要的結果，但您現在在結果集中與您的條件不匹配的行有 NULL。您有兩種方法可以擺脫它們：
選項1
將您的CASE陳述句復制到您的WHERE子句并使用它來過濾掉NULLs。
Pros: You have one 'SELECT` statement. No real benefit here unless you just prefer the way it looks.
Cons: Harder to read code and if you modify this logic later, you have to remember to update the logic in both places. IMHO, the con in this one is a big con. The chances of this happening are high. I see it happen all of the time when people are making quick updates to code.
SELECT
CustomerID = CASE
WHEN NOT EXISTS (SELECT * FROM ProductCustomers) THEN
ic.CustomerID
WHEN NOT EXISTS (SELECT * FROM IndustryCustomers) THEN
pc.CustomerID
WHEN EXISTS (SELECT * FROM IndustryCustomers)
AND EXISTS (SELECT * FROM ProductCustomers)
AND ic.CustomerID = pc.CustomerID THEN
ic.CustomerID
END
FROM IndustryCustomers AS ic
FULL JOIN ProductCustomers AS pc
ON pc.CustomerID = ic.CustomerID
WHERE (CASE
WHEN NOT EXISTS (SELECT * FROM ProductCustomers) THEN
ic.CustomerID
WHEN NOT EXISTS (SELECT * FROM IndustryCustomers) THEN
pc.CustomerID
WHEN EXISTS (SELECT * FROM IndustryCustomers)
AND EXISTS (SELECT * FROM ProductCustomers )
AND ic.CustomerID = pc.CustomerID THEN
ic.CustomerID
END
) IS NOT NULL;
Option 2
Wrap your query in a query that eliminates NULLS.
Pros: No duplicated logic to maintain, shorter easier to read code.
Cons: It's not a single SELECT statement, but functionally there are no cons.
SELECT
*
FROM (
SELECT
CustomerID = CASE
WHEN NOT EXISTS (SELECT * FROM ProductCustomers) THEN
ic.CustomerID
WHEN NOT EXISTS (SELECT * FROM IndustryCustomers) THEN
pc.CustomerID
WHEN EXISTS (SELECT * FROM IndustryCustomers)
AND EXISTS (SELECT * FROM ProductCustomers)
AND ic.CustomerID = pc.CustomerID THEN
ic.CustomerID
END
FROM IndustryCustomers AS ic
FULL JOIN ProductCustomers AS pc
ON pc.CustomerID = ic.CustomerID
) AS x
WHERE x.CustomerID IS NOT NULL;
Example code showing results for each scenario
I'm using a Common Table Expression (CTE) and a Table Value Constructor to build the example data. The query that selects the data is the same in each of these.
IndustryCustomers and ProductCustomers both have data
WITH
IndustryCustomers AS (
SELECT
IndustryCustomers.CustomerID
FROM ( VALUES (1), (2), (3)) AS IndustryCustomers (CustomerID)
),
ProductCustomers AS (
SELECT
ProductCustomers.CustomerID
FROM ( VALUES (2), (3), (4)) AS ProductCustomers (CustomerID)
)
SELECT
*
FROM (
SELECT
CustomerID = CASE
WHEN NOT EXISTS (SELECT * FROM ProductCustomers) THEN
ic.CustomerID
WHEN NOT EXISTS (SELECT * FROM IndustryCustomers) THEN
pc.CustomerID
WHEN EXISTS (SELECT * FROM IndustryCustomers)
AND EXISTS (SELECT * FROM ProductCustomers)
AND ic.CustomerID = pc.CustomerID THEN
ic.CustomerID
END
FROM IndustryCustomers AS ic
FULL JOIN ProductCustomers AS pc
ON pc.CustomerID = ic.CustomerID
) AS x
WHERE x.CustomerID IS NOT NULL;
| CustomerID |
|---|
| 2 |
| 3 |
ProductCustomers contains no data
WITH
IndustryCustomers AS (
SELECT
IndustryCustomers.CustomerID
FROM ( VALUES (1), (2), (3)) AS IndustryCustomers (CustomerID)
),
ProductCustomers AS (
SELECT CustomerID = NULL
WHERE 1 = 2
)
SELECT
*
FROM (
SELECT
CustomerID = CASE
WHEN NOT EXISTS (SELECT * FROM ProductCustomers) THEN
ic.CustomerID
WHEN NOT EXISTS (SELECT * FROM IndustryCustomers) THEN
pc.CustomerID
WHEN EXISTS (SELECT * FROM IndustryCustomers)
AND EXISTS (SELECT * FROM ProductCustomers)
AND ic.CustomerID = pc.CustomerID THEN
ic.CustomerID
END
FROM IndustryCustomers AS ic
FULL JOIN ProductCustomers AS pc
ON pc.CustomerID = ic.CustomerID
) AS x
WHERE x.CustomerID IS NOT NULL;
| CustomerID |
|---|
| 1 |
| 2 |
| 3 |
IndustryCustomers contains no data
WITH
IndustryCustomers AS (
SELECT CustomerID = NULL
WHERE 1 = 2
),
ProductCustomers AS (
SELECT
ProductCustomers.CustomerID
FROM ( VALUES (2), (3), (4)) AS ProductCustomers (CustomerID)
)
SELECT
*
FROM (
SELECT
CustomerID = CASE
WHEN NOT EXISTS (SELECT * FROM ProductCustomers) THEN
ic.CustomerID
WHEN NOT EXISTS (SELECT * FROM IndustryCustomers) THEN
pc.CustomerID
WHEN EXISTS (SELECT * FROM IndustryCustomers)
AND EXISTS (SELECT * FROM ProductCustomers)
AND ic.CustomerID = pc.CustomerID THEN
ic.CustomerID
END
FROM IndustryCustomers AS ic
FULL JOIN ProductCustomers AS pc
ON pc.CustomerID = ic.CustomerID
) AS x
WHERE x.CustomerID IS NOT NULL;
| CustomerID |
|---|
| 2 |
| 3 |
| 4 |
Extra notes
When using EXISTS clause always use the form SELECT * FROM .... Not only is the intent of the code more clear, but there are no performance differences between using *, 1, TOP 1 1, or Column1, ..., Column327. SQL Server stops executing the query as soon as it find a single result and never even considers the TOP. If you compare them you'll see that the execution plans are all identical.
EXISTS (SELECT 1...) vs EXISTS (SELECT TOP 1...) Does it matter?
Test with 10,000 records in each table, and only half of them overlap
SET STATISTICS IO, TIME ON
DECLARE
@IndustryStartID int = 1,
@IndustryEndID int = 10,
@ProductStartID int = 5,
@ProductEndID int = 15;
WITH
IndustryCustomers AS (
SELECT CustomerID = @IndustryStartID
UNION ALL
SELECT
ic.CustomerID 1
FROM IndustryCustomers AS ic
WHERE ic.CustomerID 1 <= @IndustryEndID
),
ProductCustomers AS (
SELECT CustomerID = @ProductStartID
UNION ALL
SELECT
pc.CustomerID 1
FROM ProductCustomers AS pc
WHERE pc.CustomerID 1 <= @ProductEndID
)
SELECT
*
FROM (
SELECT
CustomerID = CASE
WHEN NOT EXISTS (SELECT * FROM ProductCustomers) THEN
ic.CustomerID
WHEN NOT EXISTS (SELECT * FROM IndustryCustomers) THEN
pc.CustomerID
WHEN EXISTS (SELECT * FROM IndustryCustomers)
AND EXISTS (SELECT * FROM ProductCustomers)
AND ic.CustomerID = pc.CustomerID THEN
ic.CustomerID
END
FROM IndustryCustomers AS ic
FULL JOIN ProductCustomers AS pc
ON pc.CustomerID = ic.CustomerID
) AS x
WHERE x.CustomerID IS NOT NULL
OPTION (MAXRECURSION 10000);
SET STATISTICS IO, TIME OFF
uj5u.com熱心網友回復：
就我個人而言，我可能會使用@Heinz 的方法，但奇怪的是，NOT EXISTS 的性能比我的解決方案差。根據執行計劃，NOT EXISTS 似乎無緣無故地掃描整個表，不知道為什么。將不得不進一步調查發生了什么（我使用的是 SQL Server 2017 開發版）。
所以這是一個非常簡潔的解決方案，似乎比 Heinzi 和 Nick 的解決方案表現更好（在我非常有限的測驗中）
使用 APPLY 和完全連接的簡潔解決方案
SELECT FinalCustomerID = ISNULL(I.CustomerID,P.CustomerID)
FROM #IndustryCustomers AS I
FULL JOIN #ProductCustomers AS P
ON I.CustomerID = P.CustomerID
CROSS APPLY (
SELECT
HasI = CASE WHEN EXISTS (SELECT * FROM #IndustryCustomers) THEN 'Y' ELSE 'N' END
,HasP = CASE WHEN EXISTS (SELECT * FROM #ProductCustomers ) THEN 'Y' ELSE 'N' END
) AS C
WHERE ('N' NOT IN (HasI,HasP) AND I.CustomerID = P.CustomerID)
OR (HasI = 'Y' AND HasP = 'N' AND I.CustomerID IS NOT NULL)
OR (HasI = 'N' AND HasP = 'Y' AND P.CustomerID IS NOT NULL)
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/447364.html