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

select * from 表 where parent_id=0

--测试数据

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;

select * from 表 where top_parent_id=0