SQL Server 選擇具有所有一組項目的訂單

我有以下表格，Order 和 OrderLine：

Order:

id | total
----------
 1 | 55.09
 2 | 62.42

OrderLine:

order_id | line_number | item      | qty
----------------------------------------
 1       | 1           | Product A | 50
 1       | 2           | Product B | 15
 2       | 1           | Product A | 23

我正在尋找建構一個查詢，該查詢將選擇同時包含產品 A 和產品 B 的所有訂單。

一些警告：

1. 可能有多行具有產品 A 和產品 B。例如，可能有第三行order 1具有Product A
2. 也可能存在需要請求許多項目的情況，而在這種情況下不僅僅是 2 個。
3. 我也想按總量查詢。因此，例如，只有總共有超過 20 件產品 A 的訂單（跨所有行）。

我的第一個想法是對每個項目進行內部連接，例如：

SELECT
   T0.id
FROM
   Order T0
   INNER JOIN OrderLine T1 on T1.order_id = T0.id AND T1.ItemCode = 'Product A'
   INNER JOIN OrderLine T2 on T1.order_id = T0.id AND T2.ItemCode = 'Product B'
GROUP BY
   T0.id

但是，我不確定如何擴展它以能夠根據總量進行選擇。可能使用 SUM 和 HAVING？

**這是帶餘數的關係除法的典型範例，**其中除數的數量是未知的。

有很多方法可以解決這個問題，但從根本上說，巧妙地解決這個問題的關鍵是將你的輸入數據轉換成表格形式。這可以是臨時表、表變數或表值參數。

這是一種典型的關係劃分解決方案。請注意，OUTER APPLY也可以使用分組的LEFT JOIN.

DECLARE @input TABLE (item varchar(20) PRIMARY KEY, quantity int);
INSERT @input VALUES
('Product A',20),('Product B',NULL);

SELECT
 o.id
FROM Order_tbl o 
WHERE EXISTS (SELECT 1
   FROM @input i
   OUTER APPLY (
       SELECT ol.item
       FROM OrderLine ol
       WHERE ol.item = i.item AND o.id = ol.order_id
       GROUP BY
         ol.item
       HAVING SUM(ol.qty) > i.quantity OR i.quantity IS NULL
   ) ol
   HAVING COUNT(ol.item) = COUNT(*)
);

---

另一種方法是雙重方法，NOT EXISTS儘管這通常效率不高。

SELECT
 o.id
FROM Order_tbl o 
WHERE NOT EXISTS (SELECT 1
   FROM @input i
   WHERE NOT EXISTS (SELECT 1
       FROM OrderLine ol
       WHERE ol.item = i.item AND o.id = ol.order_id
       GROUP BY
         ol.item
       HAVING SUM(ol.qty) > i.quantity OR i.quantity IS NULL
   )
);

---

其他答案之一部分使用的最後一個選項是預先計算輸入值的計數，然後使用普通連接。這通常是最有效的。

再次，INNER JOIN可能是一個CROSS APPLY.

DECLARE @count int = (SELECT COUNT(*) FROM @input);

SELECT
 o.id
FROM Order_tbl o 
WHERE EXISTS (SELECT 1
   FROM (
       SELECT
         ol.item,
         ol.order_id,
         SUM(ol.qty) totalQty
       FROM OrderLine ol
       GROUP BY
         ol.item,
         ol.order_id
   ) ol
   JOIN @input i ON ol.item = i.item AND o.id = ol.order_id
   HAVING COUNT(*) = COUNT(CASE WHEN ol.totalQty > i.quantity OR i.quantity IS NULL THEN 1 END)
      AND COUNT(*) = @count
);

db<>小提琴

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