查询树形的根节点

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
             )

,/*找到没有父节点的节点，即根节点*/
        x1
          AS ( SELECT   t1.* ,
                        t2.mid AS root_flag
               FROM     x0 t1
                        LEFT JOIN x0 t2 ON t2.mid = t1.pid
             )

,/*从根节点往下递归*/
        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