父 ID 不在列表中时递归 CTE

我有以下嵌套集

结果就是这棵树

1 -
  |---- 2 -
  |       |---- 4 -
  |               |---- 7
  |               |---- 8
  |----10 -
          |---- 9
3 -
  |----  5
  |----  6
13-
  |---- 11
  |---- 12

我有一份产品清单 选择 ID、名称... 来自产品

与类别的多对多关系。所有类别都可以有促销活动。好吧，现在问题来了。

假设我有一个类别 7、8、6 的 ProductX。以及类别 1、2、3 中的促销活动。我需要让最接近的父母按类别进行促销，或者直到没有更多的父母为止。

最终结果应该是

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;对于每个产品（所以只是一个简单的连接）。

我的问题是我知道我需要使用（或者我认为我需要使用）递归 CTE，但我不知道该怎么做。因为它应该只对每一行递归，并且直到找到该行的促销为止。

编辑（我会尝试解释逻辑）。

ProductId  CategoryId
     1         7
     1         8
     1         6

该产品有 2 个直接父级：4（来自 7 和 8）和 3（来自 6） 我的 CategoryIds 有促销活动：1、2、3。 第一轮查询结果

CategoryId ParentId PromotionPrice
     7         4         NULL
     8         4         NULL 
     6         3          10

重要的是 ParentId，这样我就可以 GroupBy ParentId，结果是

CategoryId PromotionPrice
     4         NULL
     3          10

好的，由于 PromotionPrice 为 NULL，我需要转到他的父项（在本例中为 2），因此上面的查询需要返回

CategoryId ParentId PromotionPrice
     4       2         NULL
     3      NULL       10

由于 PromotionPrice 为 Null，我必须检查 Category2 是否有促销，因此结果为

CategoryId ParentId PromotionPrice
     2       1         15
     3      NULL       10

它停在那里。如果我从类别 2 中删除促销活动，则应该进行另一轮：

CategoryId ParentId PromotionPrice
     1      NULL       5
     3      NULL       10

此时，由于不再有父级，因此 PromotionPrice 是否为 null 并不重要。问题是我需要一路努力才能获得晋升。

当我查看 SortPath 时已经拥有所有信息，只需要将其分解并递归向后直到找到具有促销的 ID，但我仍然不知道如何实现这一点。

希望这有助于解释一下。

注意：我稍作编辑以反映您提供的示例数据。

设置

这是我必须代表您的嵌套集的内容：

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);

以下是我为您的促销活动构建的内容：

declare @promotions table (
    promotionId int identity(1,1),
    categoryId int,
    price float
);

insert @promotions values (1, 5), (2, 15), (3, 10);

还有您的产品，我已将其重命名为 ProductCategories 以更好地反映其内容：

declare @productCategories table (productId int, categoryId int);
insert @productCategories values (1,7),(1,8),(1,6);

解决方案

作为主播，我只是拉入了产品表。但我认为在您的用例中，您需要一个过滤器来挑选合适的基础产品。然后我做了一个计算来检查该类别是否已经是促销活动。如果是，那么它代表一个叶节点。

在递归中，我只是将每个不是叶子的节点的嵌套集的层次结构向上移动。我再次计算一下该类别是否是促销，看看它是否是叶子节点。

从结果中，我选择了所有叶节点，按价格排序，并输出最上面的一个。

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