sql - SQL Server:没有循环的排列/组合
问题描述
我有两个数据集。第一个是产品配方表以及构成配方的产品。第二个数据集包含按产品划分的单独定价(我可以为单个产品设置多个价格)。
我想要实现的是输出一个结果集,其中包含我的每个产品配方的独特排列。只有所有组件在第二个数据集中都有定价的配方才应该在输出中。
假设:一个配方最多可以配置 5 个组件(不再有)。
DECLARE @ProductRecipe TABLE (ProductRecipeID INT, ComponentProductID INT)
INSERT INTO @ProductRecipe (ProductRecipeID, ComponentProductID)
VALUES (21, 130), (21, 468), (21, 500),
(22, 468), (22, 500),
(23, 130), (23, 501)
DECLARE @ComponentPricing TABLE (PricingID INT, ProductID INT)
INSERT INTO @ComponentPricing (PricingID, ProductID)
VALUES (314023, 130), (313616, 130), (313071, 130),
(312865, 130), (316323, 468), (316329, 468), (398864, 500)
我希望我的输出看起来像这样:
输出示例
我已经尝试过 CTE 和自我连接,但我什至无法接近我想要的输出.. :(
我正在使用 SQL Server 2012
解决方案
我将假设您正在使用 SQL Server 2008 或更高版本,这是使该dense_rank()
功能正常工作所必需的。
下面的解决方案经历了评论中概述的几个步骤。一个提示是我将其中一条@ProductRecipe
记录从(22, 130)
更改(22, 468)
为 因为我认为它是预期的样本数据,因为Component1
所需的输出包括PricingID
值 316323 和 316329。
回答:
DECLARE @ProductRecipe TABLE (ProductRecipeID INT, ComponentProductID INT)
INSERT INTO @ProductRecipe (ProductRecipeID, ComponentProductID) VALUES (21, 130)
INSERT INTO @ProductRecipe (ProductRecipeID, ComponentProductID) VALUES (21, 468)
INSERT INTO @ProductRecipe (ProductRecipeID, ComponentProductID) VALUES (21, 500)
INSERT INTO @ProductRecipe (ProductRecipeID, ComponentProductID) VALUES (22, 468) --values were (22, 130) in question
INSERT INTO @ProductRecipe (ProductRecipeID, ComponentProductID) VALUES (22, 500)
INSERT INTO @ProductRecipe (ProductRecipeID, ComponentProductID) VALUES (23, 130)
INSERT INTO @ProductRecipe (ProductRecipeID, ComponentProductID) VALUES (23, 501)
DECLARE @ComponentPricing TABLE (PricingID INT, ProductID INT)
INSERT INTO @ComponentPricing (PricingID, ProductID)
VALUES (314023, 130)
, (313616, 130)
, (313071, 130)
, (312865, 130)
, (316323, 468)
, (316329, 468)
, (398864, 500)
; with base as
(
--Joining the two datasets together.
select pr.ProductRecipeID
, pr.ComponentProductID
, cp.PricingID
from @ProductRecipe as pr
left join @ComponentPricing as cp on pr.ComponentProductID = cp.ProductID
)
, pr_exclude as
(
--Identifying that ProductRecipeID 23 should be excluded because of the 501 NULL value
select distinct b.ProductRecipeID
from base as b
where b.PricingID is null
)
, final_base as
(
--Assigning Rank to each ComponentProductID
select b.ProductRecipeID
, b.ComponentProductID
, b.PricingID
, dense_rank() over (partition by b.ProductRecipeID order by b.ComponentProductID asc) as prod_rnk
from base as b
left join pr_exclude as p on b.ProductRecipeID = p.ProductRecipeID
where 1=1
and p.ProductRecipeID is null
)
--Joining it all together
select a.ProductRecipeID
, a.PricingID as Component1
, b.PricingID as Component2
, c.PricingID as Component3
, d.PricingID as Component4
, e.PricingID as Component5
from final_base as a
left join final_base as b on a.ProductRecipeID = b.ProductRecipeID and b.prod_rnk = 2
left join final_base as c on b.ProductRecipeID = c.ProductRecipeID and c.prod_rnk = 3
left join final_base as d on c.ProductRecipeID = d.ProductRecipeID and d.prod_rnk = 4
left join final_base as e on d.ProductRecipeID = e.ProductRecipeID and e.prod_rnk = 5
where a.prod_rnk = 1
order by 1, 2, 3, 4, 5, 6
推荐阅读
- python-3.x - 从脚本向超过 1 个收件人发送电子邮件
- c# - 试图通过数据绑定将属性更新从对象传递到 gui
- python - 如何在同时运行的两个并行进程之间创建依赖关系
- javascript - 将函数的特定变量导出到另一个文件
- php - 每个实体项的 ManyToMany 关系表中的 Symfony 查询 id
- rust - PhantomData 类型在 rust 中的使用
- ramda.js - Ramda zipWith,一个参数
- php - 循环一个数组,只有当循环的属性改变时才做一些事情
- python - 在 GAE flex 中捕获 gunicorn 错误
- r - 使用关键字在目录中找到正确的文件并在R中进行比较