sql - 使用 SQL Server CTE 返回所有父记录

Question

我有下表类层次结构。

ClassID    ParentID
--------------------
PMM_00001  null
PMM_00010  PMM_00001
PMM_00110  PMM_00010
PMM_00020  PMM_00001
PMM_00220  PMM_00020

我想得到以下结果。

ClassID    Class_Join
---------------------
PMM_00001  PMM_00001
PMM_00010  PMM_00001
PMM_00010  PMM_00010
PMM_00110  PMM_00001
PMM_00110  PMM_00010
PMM_00110  PMM_00110
PMM_00020  PMM_00001
PMM_00020  PMM_00020
PMM_00220  PMM_00001
PMM_00220  PMM_00020
PMM_00220  PMM_00220

我计划使用这些结果通过连接到我的特征表来获取继承的特征。过去我使用循环来正确设置数据，但我想使用 CTE 来获得这些结果。

这是我到目前为止尝试过的方法。

;WITH ClassHierarchy_CTE (ClassID, ClassID_Join)
AS
(
SELECT
    c.ClassID,
    c.ClassID
FROM
    ClassHierarchy AS h
WHERE
    h.ParentID IS NULL
UNION ALL
SELECT
    c.ClassID,
    h.ParentID
FROM
    ClassHierarchy AS h INNER JOIN ClassHierarchy_CTE
        ON
            h.ParentID = ClassHierarchy_CTE.ClassID
)
SELECT
    *
FROM
    ClassHierarchy_CTE
ORDER BY
    ClassID

我从中得到的结果只是沿着层次结构走下去以获得完整列表。我需要将引用返回到层次结构中的每个级别，以便我可以获得完整的特征列表。任何帮助表示赞赏!如果有更好的方法，我愿意接受建议。

Answer 1

看来我的处理方式全错了。我缺少的概念是我需要向上移动层次结构，而不是向下(正如大多数文档/文章所展示的那样)。这就是我所做的。

CREATE TABLE hierarchy
(
  ClassID  nvarchar(100), ParentID nvarchar(100)
)

INSERT INTO hierarchy
( ClassID     ,           ParentID)
VALUES
( N'PMM_00001',      NULL    ),
( N'PMM_00010',      N'PMM_00001'),
( N'PMM_00110',      N'PMM_00010'),
( N'PMM_00020',      N'PMM_00001'),
( N'PMM_00220',      N'PMM_00020')

;WITH ClassHierarchy_CTE (ClassID, ClassID_Join, Level)
AS
(
SELECT
    ClassID,
    ClassID AS Join_Class,
    0
FROM
    hierarchy AS c
UNION ALL
SELECT
    cte.ClassID,
    h.ParentID,
    Level + 1
FROM
    hierarchy AS h INNER JOIN ClassHierarchy_CTE as cte
      ON
        h.ClassID = cte.ClassID_Join
)
SELECT
    *
FROM
    ClassHierarchy_CTE
WHERE
    ClassID_Join IS NOT NULL
ORDER BY
    ClassID,
    Level

返回...

CLASSID CLASSID_JOIN    LEVEL
---------------------------------
PMM_00001   PMM_00001   0
PMM_00010   PMM_00010   0
PMM_00010   PMM_00001   1
PMM_00020   PMM_00020   0
PMM_00020   PMM_00001   1
PMM_00110   PMM_00110   0
PMM_00110   PMM_00010   1
PMM_00110   PMM_00001   2
PMM_00220   PMM_00220   0
PMM_00220   PMM_00020   1
PMM_00220   PMM_00001   2

SQLfiddle供引用。