我有张sql表
ASSN_id ASSN_name parent_id
1 a 0
2 b 1
3 c 1
4 d 0
5 e 3
6 f 2
7 g 4
就象的树型一样。
a
b
f
c
e
d
有两个根结点 :a ,d
如果我想得到的是a 和a的子节点的的全部信息,包括:ASSN_ID,ASSN_NAME,parent_ID
...全文
1005打赏收藏
怎么样实现这样的sqld的select语句??
我刚才没有问清楚 我有张sql表 ASSN_id ASSN_name parent_id 1 a 0 2 b 1 3 c 1 4 d 0 5 e 3 6 f 2 7 g 4 就象的树型一样。 a b f c e d 有两个根结点 :a ,d 如果我想得到的是a 和a的子节点的的全部信息,包括:ASSN_ID,ASSN_NAME,parent_ID
--创建处理的自定义函数
create function f_id(@name varchar(10))
returns @re table(id int,sid varchar(8000),level int)
as
begin
--为了数字排序正常,需要统一编码宽度
declare @idlen int,@idheader varchar(20)
select @idlen=max(len(ASSN_id))
,@idheader=space(@idlen)
from 表
declare @l int
set @l=0
insert into @re select ASSN_id,right(@idheader+cast(ASSN_id as varchar),@idlen),@l
from 表
where ASSN_name=@name
while @@rowcount>0
begin
set @l=@l+1
insert into @re select a.ASSN_id,b.sid+','+right(@idheader+cast(a.ASSN_id as varchar),@idlen) ,@l
from 表 a inner join @re b on a.parent_id=b.id and b.level=@l-1
end
return
end
go
--测试数据
create table 表(ASSN_id int,ASSN_name varchar(10),parent_id int)
insert 表 select 1,'a',0
union all select 2,'b',1
union all select 3,'c',1
union all select 4,'d',0
union all select 5,'e',3
union all select 6,'f',2
union all select 7,'g',4
go
--创建处理的自定义函数
create function f_id()
returns @re table(id int,sid varchar(8000),level int)
as
begin
--为了数字排序正常,需要统一编码宽度
declare @idlen int,@idheader varchar(20)
select @idlen=max(len(ASSN_id))
,@idheader=space(@idlen)
from 表
declare @l int
set @l=0
insert into @re select ASSN_id,right(@idheader+cast(ASSN_id as varchar),@idlen),@l
from 表
where parent_id=0
while @@rowcount>0
begin
set @l=@l+1
insert into @re select a.ASSN_id,b.sid+','+right(@idheader+cast(a.ASSN_id as varchar),@idlen) ,@l
from 表 a inner join @re b on a.parent_id=b.id and b.level=@l-1
end
return
end
go
--查询
select a.ASSN_id,ASSN_name=space(b.level*2)+a.ASSN_name,a.parent_id
from 表 a join f_id() b on a.ASSN_id=b.id
order by b.sid
go
drop table 表
drop function f_id
/*--测试结果
ASSN_id ASSN_name parent_id
----------- ---------- -----------
1 a 0
2 b 1
6 f 2
3 c 1
5 e 3
4 d 0
7 g 4
--创建处理的自定义函数
create function f_id()
returns @re table(id int,sid varchar(8000),level int)
as
begin
--为了数字排序正常,需要统一编码宽度
declare @idlen int,@idheader varchar(20)
select @idlen=max(len(ASSN_id))
,@idheader=space(@idlen)
from 表
declare @l int
set @l=0
insert into @re select ASSN_id,right(@idheader+cast(ASSN_id as varchar),@idlen),@l
from 表
where parent_id=0
while @@rowcount>0
begin
set @l=@l+1
insert into @re select a.ASSN_id,b.sid+','+right(@idheader+cast(a.ASSN_id as varchar),@idlen) ,@l
from 表 a inner join @re b on a.parent_id=b.id and b.level=@l-1
end
return
end
go
--查询
select a.ASSN_id,ASSN_name=space(b.level*2)+a.ASSN_name,a.parent_id
from 表 a join f_id() b on a.ASSN_id=b.id
order by b.sid
查询
select a.ASSN_id,a.ASSN_name,a.ASSN_parent_id
from TB_Project_ASSn a join f_id('需求分析') b on a.ASSN_id=b.id
order by b.sid
go
/////////////////////////////////////////////