34,594
社区成员
发帖
与我相关
我的任务
分享
With T1(ID,PID,Name) As
(
Select 1,0,'1' Union All
Select 2,0,'2' Union All
Select 3,0,'3' Union All
Select 4,1,'1-1' Union All
Select 5,1,'1-2' Union All
Select 6,4,'1-1-1' Union All
Select 7,4,'1-1-2' Union All
Select 8,2,'2-1' Union All
Select 9,3,'3-1'
),T2 As
(
Select *,0 As Lev from T1 where PID=0 Union All
Select a.*,Lev+1 from T1 a Cross Apply T2 b where a.PID=b.ID
)
Select * from T2
with taba (ID,上级ID,名称)
as
(
select 1,0,'1' union all
select 2,0,'2' union all
select 3,0,'3' union all
select 4,1,'1-1' union all
select 5,1,'1-2' union all
select 6,4,'1-1-1' union all
select 7,4,'1-1-2' union all
select 8,2,'2-1' union all
select 9,3,'3-1'
)
SELECT *,len(名称)-len(replace(名称,'-','')) AS '级数'
FROM taba