如何在 T-SQL 中使用递归 CTE 获取完整的层次结构?

Question

表：

+-----+------------+-------------+
| Id  | DocumentNo | ParentCCID  |
+-----+------------+-------------+
|  10 | CC001      | NULL        |
|  20 | CC002      | CC001       |
|  33 | CC003      | CC002       |
+-----+-------------+-------------+

传递给查询的值：CC002

预期输出：

CC003
CC002
CC001

以下查询仅获取层次结构中排名较高的记录：

;WITH cte AS (
SELECT Id,
documentno,
DocumentTypeID,
ParentCCID
FROM basicdetails
WHERE DocumentNo = 'CC#000056'
UNION ALL
SELECT R.Id,
R.DocumentNo,
R.DocumentTypeID,
R.ParentCCID
FROM basicdetails R
JOIN cte P ON P.ParentCCID = R.DocumentNo
)
SELECT Id,
documentno,     
ParentCCID
FROM cte
ORDER BY 1 DESC

注： 文档编号是唯一的主键。如果没有父记录，则父CCID可能具有空值。

create table basicdetails
(
id int identity,
documentno varchar(30),
parentccid varchar(30)
)
insert into basicdetails values('CC001', null)
insert into basicdetails values('CC002', 'CC001')
insert into basicdetails values('CC003', 'CC002')
insert into basicdetails values('CC004', 'CC003')

Answer 1

我想我找到了答案。

;WITH cte AS (
SELECT Id,
documentno,
DocumentTypeID,
ParentCCID
FROM basicdetails
WHERE DocumentNo = 'CC02'
UNION ALL
SELECT R.Id,
R.DocumentNo,
R.DocumentTypeID,
R.ParentCCID
FROM basicdetails R
JOIN cte P ON P.ParentCCID = R.DocumentNo
),
cte2 AS (
SELECT Id,
documentno,
DocumentTypeID,
ParentCCID
FROM basicdetails
WHERE DocumentNo = 'CC002'
UNION ALL
SELECT R.Id,
R.DocumentNo,
R.DocumentTypeID,
R.ParentCCID
FROM basicdetails R
JOIN cte2 P ON P.DocumentNo = R.ParentCCID
)

SELECT Id,
documentno,
DocumentTypeID,
ParentCCID
FROM cte     
union   
SELECT Id,
documentno,
DocumentTypeID,
ParentCCID
FROM cte2    

虽然我不确定这是否是最好的方法。它确实有效。我只需要逆转JOIN cte P ON P.ParentCCID = R.DocumentNo和执行UNION