双编号处理方法

--广度排序
create table #Info
(
  ID char(3),
  PID char(3),
  [Name] varchar(20)
)
insert into #Info select '001',null,'山东省'
union all select '002','001','烟台市'
union all select '004','002','招远市'
union all select '003','001','青岛市'
union all select '005',null,'四会市'
union all select '006','005','清远市'
union all select '007','006','小分市'

create table #TT
(
  ID char(3),
  [Level] int
)
declare @level int
set @level=0
insert into #TT select ID,@level from #Info where PID is null
--select * from #TT
while @@rowcount>0
begin
  set @level=@level+1
  insert into #TT select I.ID,@level from #Info I,#TT T where I.PID=T.ID and [Level]=@level-1
end

select I.* from #Info I join #TT T on I.ID=T.ID order by T.[level]

 

ID   PID  Name
---- ---- --------------------
001  NULL 山东省
005  NULL 四会市
002  001  烟台市
003  001  青岛市
006  005  清远市
004  002  招远市
007  006  小分市

(7 行受影响)

--查找子节点

create table Info
(
  ID char(3),
  PID char(3),
  [Name] varchar(20)
)
insert into Info select '001',null,'山东省'
union all select '002','001','烟台市'
union all select '004','002','招远市'
union all select '003','001','青岛市'
union all select '005',null,'四会市'
union all select '006','005','清远市'
union all select '007','006','小分市'

create function dbo.f_id(@id char(3))
returns @tb_level table(id char(3),[level] int)
as
begin
 declare @level int
  set @level=0
 insert into @tb_level select @id,@level
 while @@rowcount>0
 begin
   set @level=@level+1;
   insert into @tb_level select I.ID,@level from Info I,@tb_level tb where I.PID=tb.ID and [level]=@level-1
 end
return
end 

select I.* from Info I,dbo.f_id('002') F where I.ID=F.ID

ID   PID  Name
---- ---- --------------------
002  001  烟台市
004  002  招远市

(2 行受影响)