如何获取每个产品所属的所有父级和子级类别？

Question

我有3个表格：

CREATE TABLE [dbo].[ProductCategory](
 [categoryID] [int] IDENTITY(1,1) NOT NULL,
 [categoryParentID] [int] NULL,
 [categoryName] [nvarchar](200) NOT NULL
)

CREATE TABLE [dbo].[Product]
(
 [productID] [bigint] IDENTITY(1,1) NOT NULL,
 [productName] [nvarchar(100)] NOT NULL,
 .
 .
)

CREATE TABLE [dbo].[ProductToCategoryLink]
(
 [productID] [bigint] NOT NULL,
 [categoryID] [int] NOT NULL
)

示例数据：

产品：

1，'Book_1';

2，'Book_2';

产品类别：

1，NULL，'图书';

2，1，'图书子类别级别1';

3，2，'图书子类别级别2';

4，3，'图书子类别级别3';

ProductToCategoryLink：

1，4;

2，2;

问题：如何获取每个产品所属的所有父级和子级类别？

因此，我需要获得类似于这样的内容：

---

产品ID，产品名称，类别ID，类别名称

1，'书籍_1'，1，'书籍';

1，'书籍_1'，2，'一级书籍子类别';

1，'书籍_1'，3，'二级书籍子类别';

1，'书籍_1'，4，'三级书籍子类别';

2，'书籍_2'，1，'书籍';

2，'书籍_2'，2，'一级书籍子类别';

- gyromonotron

Answer 1

最简单的方法是通过公共表达式来实现。使用直接链接作为锚点，然后进行两次扩展：首先扩展找到的锚点的父级，然后使用第二个union扩展所有子级。 >链接<

- TToni

---

非常酷，以前从未听说过CTE。这在一些旧项目上会很有用 :) - CodeMonkey1313

Answer 2

如果详细说明的话，本帖底部展示的SQL查询语句应该可以解决问题。

关键特征是层次结构表达式的递归定义。第一个SELECT检索所有产品-类别链接，以及它们的名称和类别父ID。这是递归的基本情况。第二个SELECT查找前一步中找到的所有行的所有类别父行。特别注意，第二个SELECT再次加入到层次结构表达式中，使查询递归。SQL Server将重复评估第二个SELECT，直到不再找到新记录。第三个SELECT语句只是返回结果。

正如您所预期的那样，如果类别父链中存在循环，那将是一件糟糕的事情。SQL Server在放弃查询之前会限制递归的次数。默认限制为100，除非类别层次结构非常深，否则这可能足够了。如果需要，您可以使用MAXRECURSION查询选项将其提高到32767。

以下是SQL查询语句：

WITH
  hierarchy AS (
    SELECT
      prod.productId
    , prod.productName
    , cat.categoryId
    , cat.categoryParentId
    , cat.categoryName
    FROM dbo.ProductToCategoryLink AS link
    INNER JOIN dbo.Product AS prod
      ON prod.productId = link.productId
    INNER JOIN dbo.ProductCategory AS cat
      ON cat.categoryId = link.categoryId
    UNION ALL
    SELECT
      child.productId
    , child.productName
    , parent.categoryId
    , parent.categoryParentId
    , parent.categoryName
    FROM hierarchy AS child
    INNER JOIN dbo.ProductCategory AS parent
      ON parent.categoryId = child.categoryParentId
  )
SELECT
  productId
, productName
, categoryId
, categoryName
FROM hierarchy
ORDER BY
  productId
, categoryId

- WReach