34,587
社区成员
发帖
与我相关
我的任务
分享
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