请教一个查所有子节点的最顶层节点SQL写法

[jxk]

输入数据如下：
id name parent_id level
1 '江苏省' 0 0
2 '南京市' 1 1
3 '玄武区' 2 2
4 '鼓楼区' 2 2
5 '浙江省' 0 0
6 '杭州市' 5 1
7 '西湖区' 6 2
8 '滨江区' 6 2
9 '苏州市' 1 1
10 '吴中区' 9 2
11 '吴江区' 9 2

查出所有节点的最顶层节点
id name parent_id level top_parent_id
1 '江苏省' 0 0 0
2 '南京市' 1 1 1
3 '玄武区' 2 2 1
4 '鼓楼区' 2 2 1
5 '浙江省' 0 0 0
6 '杭州市' 5 1 5
7 '西湖区' 6 2 5
8 '滨江区' 6 2 5
9 '苏州市' 1 1 1
10 '吴中区' 9 2 1
11 '吴江区' 9 2 1

请帮忙看下这个SQL该怎么写，多谢！

[吉普赛的歌]

select * from 表 where top_parent_id=0

[jxk]

top_parent_id 这一列是要SQL计算出来的

[二月十六]

用cte来做递归查询实现，代码如下：

--测试数据
if not object_id(N'Tempdb..#T') is null
	drop table #T
Go
Create table #T([id] int,[name] nvarchar(23),[parent_id] int,[level] int)
Insert #T
select 1,N'江苏省',0,0 union all
select 2,N'南京市',1,1 union all
select 3,N'玄武区',2,2 union all
select 4,N'鼓楼区',2,2 union all
select 5,N'浙江省',0,0 union all
select 6,N'杭州市',5,1 union all
select 7,N'西湖区',6,2 union all
select 8,N'滨江区',6,2 union all
select 9,N'苏州市',1,1 union all
select 10,N'吴中区',9,2 union all
select 11,N'吴江区',9,2
Go
--测试数据结束
;WITH cte AS (
SELECT  * ,
        parent_id AS top_parent_id ,
        id AS child_parent_id
FROM    #T
WHERE   parent_id = 0
UNION ALL
SELECT  #T.* ,
        cte.child_parent_id AS top_parent_id ,
        cte.child_parent_id
FROM    #T
        JOIN cte ON cte.id = #T.parent_id
)
SELECT  id ,
        name ,
        cte.parent_id ,
        cte.level ,
        cte.top_parent_id
FROM    cte
ORDER BY cte.id;

[吉普赛的歌]

引用 2 楼 kyo1979 的回复:

top_parent_id 这一列是要SQL计算出来的

还是不明白你意思， 如果讲顶级， 那就是：

select * from 表 where parent_id=0

按你的数据， 基本省级就是顶级了， 有些级的顶级不是省级是怎么个意思？

[「已注销」]

我看不懂我慢慢看