数据库环境:SQL SERVER 2005
有一个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次才实现结果。期待有大神提出更好的解决方法。