如何轻松地将此 Sql Server 层次结构展平为继承的包含列表? [英] How can I easily flatten this Sql Server hierarchy into an inherited inclusive list?

问题描述

我有如下表格(为简单起见):

I have tables (for simplicity sake) as outlined below:

Category
--------------------
CategoryId (0 for root nodes)
ParentCategoryId

ProductCategory
--------------------
ProductId
CategoryId

我希望能够检索所有类别及其继承产品的不同列表(一直回到类别 0).类别 0 应包括所有产品，所有其他类别应在层次结构中尽可能深入.

I'd like to be able to retrieve a distinct list of all categories and their inherited products (all the way back to category 0). Category 0 should include ALL products, and all other categories should follow down the hierarchy as infinitely deep as it goes.

示例表格内容:

CategoryId, ParentCategoryId
---------------------
1, 0
2, 0
3, 0
10, 1
20, 2

ProductId, CategoryId
---------------------
1, 10
2, 1
3, 2
4, 20
5, 3

我希望输出在层次结构中向上移动，并告诉我产品可能属于的每个类别.所以想要的结果应该是这样的:

I'd like the output to travel up the heirarchy and tell me every category that a product can fall under. So a desired result would look something like this:

ProductId, CategoryId
---------------------
1, 0
2, 0
3, 0
4, 0
5, 0
1, 1
2, 1
3, 2
4, 2
5, 3
1, 10
4, 20

在 SQL Server 2005 中是否有一种简单的方法可以做到这一点?

Is there an easy way to do this in SQL Server 2005?

推荐答案

您可以使用递归公用表表达式 (cte) 执行此操作.

You can do this with a recursive common table expression (cte).

WITH X (ProductId, CategoryId) AS (
    SELECT ProductId, CategoryId FROM #ProductCategory
    UNION ALL
    SELECT X.ProductId, C.ParentCategoryId FROM X
    INNER JOIN #Category C ON X.CategoryId = C.CategoryId
)
SELECT ProductId, CategoryId FROM X ORDER BY CategoryId, ProductId

更多信息位于 http://msdn.microsoft.com/en-我们/图书馆/ms186243.aspx

这篇关于如何轻松地将此 Sql Server 层次结构展平为继承的包含列表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！