可以呀,给你看个类似的例子,试试就可以了create table testbom (superId int, parentId int, name varchar(20))
insert testbom
select 1,-1,'公司'
union
select 2,1,'行政部'
union
select 3,1,'财务部'
union
select 4,2,'人事科'
union
select 5,4,'人事股'begin tran
select id=identity(int),
a.*,
currCode = cast('' as varchar(1000)),
ParentCode = cast('' as varchar(1000))
into #tm
from testbom a
declare @totalrow int
set @totalrow = @@rowcount
update #tm set currCode = convert(varchar,id) declare @loop int
declare @id int
declare @next varchar(20)
declare @curr varchar(20)
set @loop = 1
while (@loop<@totalrow)
begin
select @id=currCode,@next=parentID,@curr=superid
from #tm
where id = @loop update #tm set parentCode = isnull(ParentCode,'')+convert(varchar,@id),currcode=isnull(ParentCode,'')+convert(varchar,@id)+currcode
where parentID = @curr and id > @loop set @loop = @loop+1
end declare @findrootCode varchar(20)
select @findrootCode=currCode from #tm where superid = 2 --查行政部下属的所有部门
select superid,parentid,name from #tm where currCode like @findrootCode+'%'
rollback tran
insert testbom
select 1,-1,'公司'
union
select 2,1,'行政部'
union
select 3,1,'财务部'
union
select 4,2,'人事科'
union
select 5,4,'人事股'begin tran
select id=identity(int),
a.*,
currCode = cast('' as varchar(1000)),
ParentCode = cast('' as varchar(1000))
into #tm
from testbom a
declare @totalrow int
set @totalrow = @@rowcount
update #tm set currCode = convert(varchar,id) declare @loop int
declare @id int
declare @next varchar(20)
declare @curr varchar(20)
set @loop = 1
while (@loop<@totalrow)
begin
select @id=currCode,@next=parentID,@curr=superid
from #tm
where id = @loop update #tm set parentCode = isnull(ParentCode,'')+convert(varchar,@id),currcode=isnull(ParentCode,'')+convert(varchar,@id)+currcode
where parentID = @curr and id > @loop set @loop = @loop+1
end declare @findrootCode varchar(20)
select @findrootCode=currCode from #tm where superid = 2 --查行政部下属的所有部门
select superid,parentid,name from #tm where currCode like @findrootCode+'%'
rollback tran
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货