如何有效地處理查詢可選參數的不同組合

考慮以下表格：

Table: Parts
   part_id INT PK
   part_number VARCHAR

Table: Jobs
   job_id INT PK
   job_number VARCHAR

Table: Job_Parts
   part_id INT FK PK
   job_id INT FK PK
   quantity INT

執行以下語句：

SELECT
   p.part_number AS [Part],
   j.job_number AS [Job],
   jp.quantity AS [Quantity]
FROM
   dbo.parts AS p
   LEFT OUTER JOIN
       dbo.job_parts AS jp
       ON jp.part_id = p.part_id
   Left OUTER JOIN
       dbo.jobs AS j
       ON j.job_id = jp.job_id

將給出一個可以給出如下結果的表格：

Part        |Job        |Quantity
Part1234     Job1        10
Part1234     Job2        5
Part1234     Job3        7

現在讓我們假設我在函式中有上述語句：

CREATE FUNCTION JobParts(
   @PartNum VARCHAR = NULL,
   @JobNum VARCHAR = NULL
) RETURNS TABLE AS (
   --SELECT STATEMENT
)

有沒有辦法讓我修改 select 語句中的表連接，以便：

1. 如果@PartNumor@JobNum有一個值，它會加入 wherepart_number或job_number等於提供的值
2. 如果@PartNum或@JobNum是NULL，它將僅返回為零件輸入的最後一條記錄。

舉個例子，如果我輸入@JobNum='Job2'我想要的結果是：

Part        |Job        |Quantity
Part1234     Job2        5

否則， if @JobNumisNULL預設只顯示表中的最後一個條目，所以我的結果是：

Part        |Job        |Quantity
Part1234     Job3        7

我強烈建議你不要走這條路。您將業務邏輯與關係查詢混合在一起，這肯定會導致以後的許多問題，尤其是在性能方面。

請牢記“1 次查詢 1 項任務”的規則，越簡單、越細化越好。您正在嘗試編寫 1 個查詢來處理不同的任務。

在客戶端使用業務控制層、專用業務層，甚至在伺服器上的儲存過程中使用，並根據您的邏輯執行不同的專用、簡單的 SQL 查詢。

例如，在您的情況下，您可以編寫一個過程，該過程將接受兩個參數並根據提供的值執行正確的查詢。該過程的成本可以忽略不計，並且您可以獲得為每個組合創建單獨的優化查詢計劃的巨大優勢。

CREATE PROCEDURE SomeProc 
@PartNum VARCHAR,
@JobNum VARCHAR
AS
BEGIN
IF @PartNum IS NULL AND @JobNum IS NULL
-- Specific Query 1 (or execute sub-procedure) goes here
ELSE IF @PartNum IS NOT NULL AND @JobNum IS NOT NULL
-- Specific Query 2 (or execute sub-procedure) goes here
ELSE IF @PartNum IS NULL AND @JobNum IS NOT NULL
-- Specific Query 3 ... etc.
ELSE -- 
-- Generic query, not optimized
END

更新：如果您需要它來處理大量參數，我強烈建議您閱讀Erland Sommarskog 的這篇文章。這是我所知道的針對此類挑戰的最佳文章，它將為您提供多種解決方案，並對每種解決方案的優缺點進行全面分析。它很長，讀起來不容易，但你花在它上面的每一分鐘都是值得的。

在以前的類似案例中，對我來說效果很好的是一種混合方法。我從一個通用解決方案開始，您會在文章中找到一些選項。然後，我設置了一個跟踪或日誌表來記錄使用者的實際使用情況並讓它執行一段時間。儘管產品和行銷會說所有的組合都同等重要，但你會發現，在現實生活中，大多數使用者往往只使用少數幾個並重複相同的模式。然後，獲取最常用的組合，並僅為這些組合編寫特定查詢，並對使用唯一參數組合的少數實例使用通用查詢。我已將程式碼行添加到範例中。這樣您就可以兩全其美——對於大多數執行，您將獲得出色的性能，並且仍然支持所有可能的組合。

順便說一句 - 這種方法的一個有趣的副作用是，隨著時間的推移，使用者開始注意到當他們使用非標準組合時需要更長的時間，然後他們要麼抱怨它，所以你可以為這種情況編寫一個特定的查詢讓他們開心，但通常他們傾向於更多地使用常見的組合，這樣更快:-)

高溫高壓

引用自：https://dba.stackexchange.com/questions/223693