MSSQL 排列/组合 - 使用数据子集查找所有可能的匹配项

debugcn 发表于 Dev

杰拉德·斯金纳

在 MSSQL 中，我有一个表 (ProductRecipe)，其中最多包含 5 个不同的组件产品。然后我有一个包含单个组件产品及其成本的数据集。

我想要做的是找到满足我的食谱的所有不同的排列/组合。

CREATE TABLE #ProductRecipe (ProductRecipeID INT, Component1 INT, Component2 INT, Component3 INT, Component4 INT, Component5 INT)
CREATE TABLE #ComponentPricing (RowID INT, PricingID INT, ProductID INT, ProductDescription VARCHAR(50), Cost DECIMAL(18,6))

INSERT INTO #ProductRecipe (ProductRecipeID, Component1, Component2) VALUES (21, 130, 468)
INSERT INTO #ComponentPricing (RowID, PricingID, ProductID, ProductDescription, Cost)
VALUES (1, 314023, 130, 'ULS2', 1.783800)
 , (2, 313616, 130, 'ULS2', 1.783800)
 , (3, 313071, 130, 'ULS2', 1.794000)
 , (4, 312865, 130, 'ULS2', 1.789500)
 , (5, 316323, 468, 'B100', 1.550500)

SELECT * FROM #ProductRecipe
SELECT * FROM #ComponentPricing

DROP TABLE #ProductRecipe
DROP TABLE #ComponentPricing

我试图实现的结果是我最终得到了 4 种不同的配方变体，因为前 4 条记录（对于 ProductID 130）可以与最后一条记录（ProductID 468）混合。只能混合这两种产品，因为它们是我的 ProductRecipe 表中定义的两种成分产品。

预期结果：第1+5行，第2+5行，第3+5行，第4+5行；返回 PricingID 列。

ProductRecipeID Component1 Component2 Component3 Component4 Component5
21              314023     316323   
21              313616     316323   
21              313071     316323   
21              312865     316323

罗斯·布什

看看这是否有效。

DECLARE @ProductRecipe TABLE (ProductRecipeID INT, Component1 INT, Component2 INT, Component3 INT, Component4 INT, Component5 INT) 
DECLARE @ComponentPricing TABLE (RowID INT, PricingID INT, ProductID INT, ProductDescription VARCHAR(50), Cost DECIMAL(18,6)) 

INSERT INTO @ProductRecipe (ProductRecipeID, Component1, Component2,Component3) VALUES (21, 130, 468,221)
INSERT INTO @ComponentPricing (RowID, PricingID, ProductID, ProductDescription, Cost)
VALUES (1, 314023, 130, 'ULS2', 1.783800)
 , (2, 313616, 130, 'ULS2', 1.783800)
 , (3, 313071, 130, 'ULS2', 1.794000)
 , (4, 312865, 130, 'ULS2', 1.789500)
 , (5, 316323, 468, 'B100', 1.550500)
 , (6, 316322, 221, 'B1110', 1.5250500)

;WITH UnpivotedRecipe AS
(
    SELECT 
        ProductRecipeID, ComponentID
    FROM
        (SELECT * FROM @ProductRecipe) AS P
        UNPIVOT(ComponentID FOR V IN(Component1,Component2,Component3,Component4,Component5))AS UP
)
, JoinedData AS
(
    SELECT 
        ProductRecipeID, ComponentID, RowID
    FROM 
        UnpivotedRecipe R
        INNER JOIN @ComponentPricing C ON C.ProductID = R.ComponentID

)
SELECT DISTINCT J1.ComponentID,J1.RowID,J2.ComponentID FROM JoinedData J1
CROSS JOIN JoinedData J2
WHERE
    J1.ComponentID<>J2.ComponentID

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。