使用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

and I want to get the following results.

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

我从这个简单的遍历结构中得到的结果只是下一级的列表。我需要获得每个层次结构的引用，以便可以获取完整的特征列表。如果有任何帮助，将不胜感激！如果有更好的方法，请给予建议。

- JoeFletch

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

Returns...

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 可供参考。

- JoeFletch