SQL Server 物料清單 (BOM) 遞歸查詢實戰：從食譜配料到生產排程的集合運算解法

物料清單 (Bill of Materials, BOM) 遞歸查詢是製造業、餐飲供應鏈、軟體依賴分析的核心邏輯。本文以「食譜-配料-食品間」經典模型為例，展示如何用集合運算思維 (而非程序式迴圈) 解決：可用配料篩選食譜、缺料清單生成、多層級 BOM 展開、成本試算等實戰問題。

核心資料模型

-- 食譜主檔
CREATE TABLE Recipes (
  RecipeID INT PRIMARY KEY,
  RecipeName NVARCHAR(100) NOT NULL,
  Servings INT DEFAULT 4
);

-- 配料主檔
CREATE TABLE Ingredients (
  IngredientID INT PRIMARY KEY,
  IngredientName NVARCHAR(50) NOT NULL,
  UnitOfMeasure NVARCHAR(20),  -- g, ml, tbsp, tsp, pcs
  CostPerUnit DECIMAL(10,4)
);

-- 食譜-配料明細 (BOM 結構)
CREATE TABLE RecipeIngredients (
  RecipeID INT NOT NULL REFERENCES Recipes(RecipeID),
  IngredientID INT NOT NULL REFERENCES Ingredients(IngredientID),
  Quantity DECIMAL(10,3) NOT NULL,  -- 所需數量
  PRIMARY KEY (RecipeID, IngredientID)
);

-- 食品間庫存 (可用配料)
CREATE TABLE Pantry (
  IngredientID INT PRIMARY KEY REFERENCES Ingredients(IngredientID),
  QtyOnHand DECIMAL(10,3) NOT NULL DEFAULT 0,
  LastRestocked DATE
);

-- 擴展：多層級 BOM (半成品)
CREATE TABLE SubAssemblies (
  ParentItemID INT NOT NULL,  -- 父項 (成品/半成品)
  ChildItemID INT NOT NULL,   -- 子項 (半成品/原料)
  QtyPerParent DECIMAL(10,3) NOT NULL,
  PRIMARY KEY (ParentItemID, ChildItemID)
);

問題一：可製作食譜篩選 (集合差集思維)

邏輯：食譜所需配料集合 ⊆ 食品間可用配料集合

步驟 1：計算每個食譜所需配料種類數

CREATE VIEW v_Recipe_Required_Count AS
SELECT RecipeID, COUNT(*) AS RequiredKindCount
FROM RecipeIngredients
GROUP BY RecipeID;

步驟 2：計算每個食譜可滿足的配料種類數 (左連接保留無庫存配料)

CREATE VIEW v_Recipe_Available_Count AS
SELECT ri.RecipeID, COUNT(p.IngredientID) AS AvailableKindCount
FROM RecipeIngredients ri
LEFT JOIN Pantry p ON ri.IngredientID = p.IngredientID AND p.QtyOnHand > 0
GROUP BY ri.RecipeID;

步驟 3：內連接兩視圖，篩選「所需 = 可用」的食譜

SELECT r.RecipeID, r.RecipeName, rc.RequiredKindCount, ac.AvailableKindCount
FROM Recipes r
INNER JOIN v_Recipe_Required_Count rc ON r.RecipeID = rc.RecipeID
INNER JOIN v_Recipe_Available_Count ac ON r.RecipeID = ac.RecipeID
WHERE rc.RequiredKindCount = ac.AvailableKindCount;  -- 核心條件：集合包含關係成立

進階：考慮數量充足性 (QtyOnHand ≥ Quantity)

CREATE VIEW v_Recipe_Available_Qty AS
SELECT ri.RecipeID, COUNT(*) AS SufficientKindCount
FROM RecipeIngredients ri
JOIN Pantry p ON ri.IngredientID = p.IngredientID
WHERE p.QtyOnHand >= ri.Quantity  -- 數量滿足條件
GROUP BY ri.RecipeID;

SELECT r.RecipeID, r.RecipeName
FROM Recipes r
JOIN v_Recipe_Required_Count rc ON r.RecipeID = rc.RecipeID
JOIN v_Recipe_Available_Qty aq ON r.RecipeID = aq.RecipeID
WHERE rc.RequiredKindCount = aq.SufficientKindCount;

問題二：缺料採購清單生成 (集合差集 + 聚合)

場景：計劃製作鳳梨酥、墨西哥沙拉、啤酒雞，生成彙總採購單

-- 目標食譜清單 (可來自參數表/暫存表)
DECLARE @TargetRecipes TABLE (RecipeID INT PRIMARY KEY);
INSERT @TargetRecipes VALUES (101), (102), (103);  -- 鳳梨酥、墨西哥沙拉、啤酒雞

-- 1. 彙總所需總量
WITH RequiredTotal AS (
  SELECT IngredientID, SUM(Quantity) AS TotalRequired
  FROM RecipeIngredients
  WHERE RecipeID IN (SELECT RecipeID FROM @TargetRecipes)
  GROUP BY IngredientID
),
-- 2. 當前庫存
CurrentStock AS (
  SELECT IngredientID, QtyOnHand
  FROM Pantry
)
-- 3. 計算缺口 (僅輸出缺口 > 0)
SELECT i.IngredientName, rt.TotalRequired, 
       COALESCE(cs.QtyOnHand, 0) AS OnHand,
       (rt.TotalRequired - COALESCE(cs.QtyOnHand, 0)) AS ShortageQty,
       u.UnitOfMeasure
FROM RequiredTotal rt
LEFT JOIN CurrentStock cs ON rt.IngredientID = cs.IngredientID
JOIN Ingredients i ON rt.IngredientID = i.IngredientID
JOIN UnitOfMeasure u ON i.UnitOfMeasureID = u.UnitOfMeasureID
WHERE rt.TotalRequired > COALESCE(cs.QtyOnHand, 0)
ORDER BY ShortageQty DESC;

問題三：多層級 BOM 遞歸展開 (CTE 遞歸)

需求：成品 A → 半成品 B,C → 原料 D,E,F 完整展開，計算單位成品所需原料總量

WITH BOM_Explosion AS (
  -- 錨點成員: 成品本身
  SELECT 
    ParentItemID AS RootItemID,
    ChildItemID AS ItemID,
    QtyPerParent AS QtyPerRoot,
    1 AS Level,
    CAST(ChildItemID AS VARCHAR(MAX)) AS Path
  FROM SubAssemblies
  WHERE ParentItemID = @TargetProductID  -- 參數化成品 ID
  
  UNION ALL
  
  -- 遞歸成員: 向下展開
  SELECT 
    be.RootItemID,
    sa.ChildItemID,
    be.QtyPerRoot * sa.QtyPerParent,  -- 累乘數量
    be.Level + 1,
    be.Path + '→' + CAST(sa.ChildItemID AS VARCHAR(20))
  FROM BOM_Explosion be
  JOIN SubAssemblies sa ON be.ItemID = sa.ParentItemID
  WHERE be.Level < 20  -- 防無限遞歸保護
)
-- 彙總至原料層 (假設原料表 Ingredients 定義了 IsRawMaterial=1)
SELECT 
  i.IngredientID, i.IngredientName,
  SUM(be.QtyPerRoot) AS TotalQtyPerUnit,
  i.UnitOfMeasure,
  i.CostPerUnit,
  SUM(be.QtyPerRoot) * i.CostPerUnit AS MaterialCostPerUnit
FROM BOM_Explosion be
JOIN Ingredients i ON be.ItemID = i.IngredientID
WHERE i.IsRawMaterial = 1
GROUP BY i.IngredientID, i.IngredientName, i.UnitOfMeasure, i.CostPerUnit
ORDER BY MaterialCostPerUnit DESC;

問題四：單位換算與成本試算 (單位轉換表)

CREATE TABLE UnitConversion (
  FromUnit NVARCHAR(20),
  ToUnit NVARCHAR(20),
  Factor DECIMAL(18,6) NOT NULL,  -- 1 FromUnit = Factor ToUnit
  PRIMARY KEY (FromUnit, ToUnit)
);
-- 範例: 1 kg = 1000 g, 1 lb = 453.592 g, 1 cup = 236.588 ml

-- 在查詢中應用單位統一 (以基準單位 g/ml 為準)
SELECT 
  i.IngredientName,
  SUM(be.QtyPerRoot * COALESCE(uc.Factor, 1)) AS TotalBaseQty,  -- 統一至基準單位
  u.BaseUnit AS Unit
FROM BOM_Explosion be
JOIN Ingredients i ON be.ItemID = i.IngredientID
JOIN UnitOfMeasure u ON i.UnitOfMeasureID = u.UnitOfMeasureID
LEFT JOIN UnitConversion uc ON u.UnitName = uc.FromUnit AND uc.ToUnit = u.BaseUnit
WHERE i.IsRawMaterial = 1
GROUP BY i.IngredientName, u.BaseUnit;

效能優化關鍵點

擴展應用場景

• 軟體依賴分析：NuGet/NPM 套件依賴圖 → 循環依賴檢測、版本衝突解析、安全漏洞傳播路徑
• 組織架構授權：員工-主管遞歸 → 權限繼承、資料範圍過濾、審批鏈路生成
• 網路拓撲追蹤：設備連接圖 → 故障影響範圍、備用路徑計算、容量規劃
• 金融風險傳染：機構持股關係 → 風險敞口聚合、壓力測試傳導、系統重要性評分

架構師感悟：BOM 問題的本質是「有向無環圖 (DAG) 上的聚合運算」。SQL 的 CTE 遞歸 + 集合運算 (JOIN/GROUP BY) 是表達此類邏輯的最原生、最高效方式。拒絕在應用層用迴圈查詢資料庫 (N+1 問題)，擁抱「以 SQL 為中心」的集合思維，才能處理百萬級節點的實時遞歸需求。