?
有一个test表，其表结构及数据如下图1。其中，id是主键，mid是当前节点，pid是父节点。
?
要求：查出每个节点的根节点，如图2所示。
?
?
分析：这需求实际上树形查询的扩展，我们可以先找到根节点，从根节点往下找到分支节点，
?
再从分支节点往下找叶子节点。
?
1.数据准备
?
?
WITH x0
AS ( SELECT 1 AS id ,
'A' AS mid ,
'B' AS pid
UNION ALL
SELECT 2 AS id ,
'B' AS mid ,
'C' AS pid
UNION ALL
SELECT 3 AS id ,
'C' AS mid ,
'N' AS pid
UNION ALL
SELECT 4 AS id ,
'D' AS mid ,
'E' AS pid
UNION ALL
SELECT 5 AS id ,
'E' AS mid ,
'G' AS pid
UNION ALL
SELECT 6 AS id ,
'G' AS mid ,
'K' AS pid
UNION ALL
SELECT 7 AS id ,
'J' AS mid ,
'H' AS pid
)
?
2.找到根节点
?
?
?
,/*找到没有父节点的节点，即根节点*/
x1
AS ( SELECT t1.* ,
t2.mid AS root_flag
FROM x0 t1
LEFT JOIN x0 t2 ON t2.mid = t1.pid
)
?
3.递归查询
?
?
,/*从根节点往下递归*/
x2 ( id, mid, pid, rid, way )
AS ( SELECT t1.id ,
t1.mid ,
t1.pid ,
CONVERT(VARCHAR(10), t1.pid) AS rid ,
CONVERT(VARCHAR(20), t1.pid + ',' + t1.mid) AS way
FROM x1 t1
WHERE t1.root_flag IS NULL
UNION ALL
SELECT t1.id ,
t1.mid ,
t1.pid ,
CONVERT(VARCHAR(10), LEFT(t2.way,
CHARINDEX(',', t2.way) - 1)) AS rid ,
CONVERT(VARCHAR(20), t2.way + ',' + t1.mid) AS way
FROM x1 t1
INNER JOIN x2 t2 ON t2.mid = t1.pid
)
SELECT id ,
mid ,
pid ,
rid
FROM x2
ORDER BY id
?
综合整个SQL，test表总共被扫描了4次才实现结果。期待有大神提出更好的解决方法。
