查询树形的根节点

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