你的位置:首页 > 数据库

[数据库]查询树形的根节点


数据库环境:SQL SERVER 2005

  有一个test表,其表结构及数据如下图1。其中,id是主键,mid是当前节点,pid是父节点。

要求:查出每个节点的根节点,如图2所示。

       图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       )

View Code

  2.找到根节点

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

View Code

  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

View Code

  综合整个SQL,test表总共被扫描了4次才实现结果。期待有大神提出更好的解决方法。