父ID不在列表中时的递归CTE [英] Recursive CTE while Parent Id not in a List
问题描述
我有以下嵌套集
结果就是这棵树
1 -
|---- 2 -
| |---- 4 -
| |---- 7
| |---- 8
|----10 -
|---- 9
3 -
|---- 5
|---- 6
13-
|---- 11
|---- 12
我有一个产品清单 SELECT ID，名称... 来自产品
I have a list of produts SELECT Id, Name ... FROM Products
与类别的多对多关系.所有类别都可以有促销.好的，现在是问题所在.
A many-to-many relationship with the categories. All Categories can have Promotions. Ok now the problem.
比方说，我在7、8、6类别中有一个ProductX.以及1,2,3类的促销.我需要按类别提升最亲近的父母，或者直到没有更多父母为止.
Let's say I have a ProductX in the categories 7,8,6. And Promotions in the categories 1,2,3. I need get the closest parent with a promotion per category or until there is no more parents.
最终结果应该是
CategoryId PromotionPrice
2 price...
3 price...
我有什么
WITH Promotions (CategoryId, PromotionPrice)
{
SELECT CategoryId, PromotionPrice
FROM Promotions
}
SELECT CategoryId, PromotionPrice
FROM NestedSet s1
LEFT JOIN NestedSet s2 ON s1.ParentId = s2.Id
LEFT JOIN Promotions p ON s1.CategoryId = p.CategoryId
然后获得更好的促销"(我知道该怎么做)并应用于主查询SELECT * FROM Products;对于每种产品(只需一个简单的联接).
And then get the Better Promotion (that I know how to do) and apply to the main query SELECT * FROM Products; For each product (so just a simple join).
我的问题是我知道我需要使用(或者我认为我需要使用)递归CTE，但是我不知道该怎么做.因为它应该仅对每一行都是递归的，并且直到找到该行的晋级为止.
My problem is that I know I need to use (or I think I need to use) a recursive CTE, but I have no idea how to do that. Since it should only be recursive for each line and only until it find a promotion for that row.
编辑(我将尝试解释其逻辑).
EDIT (I'll try to explain the logic).
ProductId CategoryId
1 7
1 8
1 6
该产品有2个直接父级:4个(从7和8开始)和3个(从6开始) 我在CategoryIds中有促销活动:1、2、3. 第一轮查询结果
This product have 2 direct parents: 4 (from 7 and 8) and 3 (from 6) I have promotions in the CategoryIds: 1, 2, 3. First round query result
CategoryId ParentId PromotionPrice
7 4 NULL
8 4 NULL
6 3 10
重要的是ParentId，因此我可以通过ParentId进行分组，结果将是
What matters is the ParentId so I can GroupBy ParentId and the result would be
CategoryId PromotionPrice
4 NULL
3 10
好的，因为promotionPrice为NULL，所以我需要去他的父母(在这种情况下为2)，所以上面的查询需要返回
Ok, since promotionPrice is NULL I need to go the his parent(in this case 2) so the query above would need to return
CategoryId ParentId PromotionPrice
4 2 NULL
3 NULL 10
由于PromotionPrice为空，所以我必须检查Category2是否有促销，结果将是
Since PromotionPrice is Null I have to check if there is a Promotion for the Category2 so the result would be
CategoryId ParentId PromotionPrice
2 1 15
3 NULL 10
它在那里停止.如果我将促销从Category2中删除，则应该再进行一轮:
It stops there. In case I removed the promotion from Category2 it should go another round:
CategoryId ParentId PromotionPrice
1 NULL 5
3 NULL 10
在这一点上，因为没有更多的父母，所以PromotionPrice是否为null都无关紧要.事情是我需要一直努力寻找晋升机会.
at this point since there is no more parents it doesn't matter if PromotionPrice is null or not. The thing is I need to go all the way up trying to find a promotion.
我正在查找SortPath中已经拥有的所有信息，只需要对其进行分解并递归地返回，直到找到具有提升的ID，但我仍然不知道该如何实现.
As I'm lookin the SortPath already have all the info, would only need to break it down and recursively go backwards until find a ID that has a promotion, still I'm clueless on how to achieve that.
希望这有助于解释.
推荐答案
注意:我进行了少许编辑以反映您提供的示例数据.
设置
以下是我要代表您的嵌套集的信息:
Note: I edited slightly to reflect the sample data you provided.
The Setup
Here is what I have to represent your nested set:
declare @nestedSet table (
id int,
parentId int
);
insert @nestedSet values
(1, null), (2, 1), (4, 2), (7, 4), (8, 4), (10, 1), (9, 10), (1004, 1),
(3, null), (5, 3), (6, 3),
(13, null), (11, 13), (12, 13);
这是我为您宣传的内容:
Here is what I built for your promotions:
declare @promotions table (
promotionId int identity(1,1),
categoryId int,
price float
);
insert @promotions values (1, 5), (2, 15), (3, 10);
还有您的产品，我将其重命名为productCategories以更好地反映其内容:
And your products, which I have renamed productCategories to better reflect its contents:
declare @productCategories table (productId int, categoryId int);
insert @productCategories values (1,7),(1,8),(1,6);
解决方案
作为主播，我刚刚拉了产品表.但是我认为在您的用例中，您将需要一个过滤器来选择适当的基本产品.然后，我进行了计算，以查看该类别是否已经是促销活动.如果是，那么它表示一个叶节点.
The Solution
As an anchor, I just pulled in the products table. But I think in your use case you'll want a filter to pick out the appropriate base products. Then I did a calculation to check to see if the category was already a promotion. If it was, then it represented a leaf node.
在递归中，我只是向上移动了不是叶子的每个节点的嵌套集的层次结构.我再次进行了计算，以查看该类别是否是促销活动，以查看它是否是叶节点.
In the recursion, I simply moved up the hierarchy of nested set for every node that was not a leaf. I again did the calculation to see if the category was a promotion to see if it was a leaf node or not.
从结果中，我选择了所有叶子节点(按价格排序)，然后输出最上面的一个.
From the results, I selected all leaf nodes, ordered by price, and output the top one.
declare @productId int = 1;
with
traverse as (
select categoryId,
parentId,
isLeaf = iif(exists (
select 0
from @promotions pm
where pd.categoryId = pm.categoryId
), 1, 0)
from @productCategories pd
join @nestedSet n on pd.categoryId = n.id
where pd.productId = @productId
union all
select categoryId = par.id,
par.parentId,
isLeaf = iif(exists (
select 0
from @promotions pm
where par.id = pm.categoryId
), 1, 0)
from traverse pd
join @nestedSet par on pd.parentId = par.id
where pd.isLeaf = 0
)
select
top 1 p.*
from traverse t
join @promotions p on t.categoryId = p.categoryId
where isLeaf = 1
order by p.price
这篇关于父ID不在列表中时的递归CTE的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
