declare @s varchar(20) set @s='局长' ;with cte(id,lastobject,secondobject) as ( select 1,'审核中心','局长' union all select 2,'国贸中心','局长' union all select 3,'办事处','副局长' union all select 4,'财政中心','副局长' union all select 5,'副局长','局长' ) select * from cte where secondobject=@s union all select b.* from cte a inner join cte b on a.lastobject=b.secondobject where a.secondobject=@s/* id lastobject secondobject 1 审核中心 局长 2 国贸中心 局长 5 副局长 局长 3 办事处 副局长 4 财政中心 副局长 */
declare @s varchar(20),@str varchar(800) set @s='局长' set @str='' ;with cte(id,lastobject,secondobject) as ( select 1,'审核中心','局长' union all select 2,'国贸中心','局长' union all select 3,'办事处','副局长' union all select 4,'财政中心','副局长' union all select 5,'副局长','局长' ) select @str=@str+lastobject+'。' from ( select * from cte where secondobject=@s union all select b.* from cte a inner join cte b on a.lastobject=b.secondobject where a.secondobject=@s )t select left(@str,len(@str)-1) /* 审核中心。国贸中心。副局长。办事处。财政中心 */
--bom结构,查找节点下所有子节点:create table os(id int,parentid int,desn varchar(10)) insert into os select 1,0,'体育用品' insert into os select 2,0,'户外运动' insert into os select 3,1,'篮球' insert into os select 4,1,'足球' insert into os select 5,2,'帐篷' insert into os select 6,2,'登山鞋' insert into os select 7,0,'男士用品' insert into os select 8,7,'刮胡刀' insert into os select 9,3,'大号篮球'--求个节点下所有子节点: IF object_id('f_cid') IS NOT NULL DROP function dbo.f_cid go create function f_cid(@id int) returns varchar(500) as begin declare @t table(id int,parentid int,desn varchar(10),lev int) declare @lev int set @lev=1 insert into @t select *,@lev from os where id=@id while(@@rowcount>0) begin set @lev=@lev+1 insert into @t select a.*,@lev from os a,@t b where a.parentid=b.id and b.lev=@lev-1 end declare @cids varchar(500) select @cids=isnull(@cids+'。','')+ltrim(id) from @t order by lev return @cids end go--调用函数 select *,ids=dbo.f_cid(id) from os WHERE id=1DROP TABLE os go/*id parentid desn ids ----------- ----------- ---------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 1 0 体育用品 1。3。4。9(1 行受影响) */
改为临时表: create table #tb(id int,lastobject varchar(10),secondobject varchar(10)) insert into #tb select 1,'审核中心','局长' union all select 2,'国贸中心','局长' union all select 3,'办事处','副局长' union all select 4,'财政中心','副局长' union all select 5,'副局长','局长'declare @s varchar(20),@str varchar(800) select @s='局长',@str=''select @str=@str+lastobject+'。' from ( select * from #tb where secondobject=@s union all select b.* from #tb a inner join #tb b on a.lastobject=b.secondobject where a.secondobject=@s )t select left(@str,len(@str)-1) drop table #tb /* 审核中心。国贸中心。副局长。办事处。财政中心 */
create table cz (id int,lastobject varchar(10),secondobject varchar(10))insert into cz select 1,'审核中心','局长' union all select 2,'国贸中心','局长' union all select 3,'办事处','副局长' union all select 4,'财政中心','副局长' union all select 5,'副局长','局长' create function dbo.fn_cz (@x varchar(10)) returns varchar(200) as begin declare @r varchar(200) declare @t table(c varchar(10))
insert into @t(c) select lastobject from cz where secondobject=@x while(@@rowcount>0) begin insert into @t(c) select a.lastobject from cz a inner join @t b on a.secondobject=b.c where not exists(select 1 from @t d where d.c=a.lastobject) end
select @r=isnull(@r,'')+'。'+c from @t return stuff(@r,1,1,'') end select dbo.fn_cz('局长') 'return'/* return ------------------------------------------- 审核中心。国贸中心。副局长。办事处。财政中心(1 row(s) affected) */
set @s='局长'
;with cte(id,lastobject,secondobject) as
(
select 1,'审核中心','局长'
union all select 2,'国贸中心','局长'
union all select 3,'办事处','副局长'
union all select 4,'财政中心','副局长'
union all select 5,'副局长','局长'
)
select * from cte where secondobject=@s
union all
select b.*
from cte a
inner join cte b on a.lastobject=b.secondobject
where a.secondobject=@s/*
id lastobject secondobject
1 审核中心 局长
2 国贸中心 局长
5 副局长 局长
3 办事处 副局长
4 财政中心 副局长
*/
set @s='局长'
set @str=''
;with cte(id,lastobject,secondobject) as
(
select 1,'审核中心','局长'
union all select 2,'国贸中心','局长'
union all select 3,'办事处','副局长'
union all select 4,'财政中心','副局长'
union all select 5,'副局长','局长'
)
select @str=@str+lastobject+'。'
from
(
select * from cte where secondobject=@s
union all
select b.*
from cte a
inner join cte b on a.lastobject=b.secondobject
where a.secondobject=@s
)t
select left(@str,len(@str)-1)
/*
审核中心。国贸中心。副局长。办事处。财政中心
*/
insert into os select 1,0,'体育用品'
insert into os select 2,0,'户外运动'
insert into os select 3,1,'篮球'
insert into os select 4,1,'足球'
insert into os select 5,2,'帐篷'
insert into os select 6,2,'登山鞋'
insert into os select 7,0,'男士用品'
insert into os select 8,7,'刮胡刀'
insert into os select 9,3,'大号篮球'--求个节点下所有子节点:
IF object_id('f_cid') IS NOT NULL
DROP function dbo.f_cid
go
create function f_cid(@id int)
returns varchar(500)
as
begin
declare @t table(id int,parentid int,desn varchar(10),lev int)
declare @lev int
set @lev=1
insert into @t select *,@lev from os where id=@id
while(@@rowcount>0)
begin
set @lev=@lev+1
insert into @t select a.*,@lev from os a,@t b
where a.parentid=b.id and b.lev=@lev-1
end
declare @cids varchar(500)
select @cids=isnull(@cids+'。','')+ltrim(id) from @t order by lev
return @cids
end
go--调用函数
select *,ids=dbo.f_cid(id) from os WHERE id=1DROP TABLE os
go/*id parentid desn ids
----------- ----------- ---------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 0 体育用品 1。3。4。9(1 行受影响)
*/
create table #tb(id int,lastobject varchar(10),secondobject varchar(10))
insert into #tb
select 1,'审核中心','局长'
union all select 2,'国贸中心','局长'
union all select 3,'办事处','副局长'
union all select 4,'财政中心','副局长'
union all select 5,'副局长','局长'declare @s varchar(20),@str varchar(800)
select @s='局长',@str=''select @str=@str+lastobject+'。'
from
(
select * from #tb where secondobject=@s
union all
select b.*
from #tb a
inner join #tb b on a.lastobject=b.secondobject
where a.secondobject=@s
)t
select left(@str,len(@str)-1)
drop table #tb
/*
审核中心。国贸中心。副局长。办事处。财政中心
*/
create table cz
(id int,lastobject varchar(10),secondobject varchar(10))insert into cz
select 1,'审核中心','局长' union all
select 2,'国贸中心','局长' union all
select 3,'办事处','副局长' union all
select 4,'财政中心','副局长' union all
select 5,'副局长','局长'
create function dbo.fn_cz
(@x varchar(10))
returns varchar(200)
as
begin
declare @r varchar(200)
declare @t table(c varchar(10))
insert into @t(c)
select lastobject
from cz where secondobject=@x while(@@rowcount>0)
begin
insert into @t(c)
select a.lastobject
from cz a
inner join @t b on a.secondobject=b.c
where not exists(select 1 from @t d where d.c=a.lastobject)
end
select @r=isnull(@r,'')+'。'+c from @t return stuff(@r,1,1,'')
end
select dbo.fn_cz('局长') 'return'/*
return
-------------------------------------------
审核中心。国贸中心。副局长。办事处。财政中心(1 row(s) affected)
*/