数据库环境:SQL SERVER 2005 有一个test表,其表结构及数据如下图1。其中,id是主键,mid是当前节点,pid是父节点。要求:查出每个节点的根节点,如图2所示。 分析:这需求实际上树形查询的扩展,我们可以先找到根节点,从根节点往 ...
数据库环境: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 )
原标题:查询树形的根节点
关键词:
*特别声明:以上内容来自于网络收集,著作权属原作者所有,如有侵权,请联系我们:
admin#shaoqun.com
(#换成@)。