create function bom1 (@id int)
returns @tb table (dirid int,dir nvarchar(10),dirparentid int)
as
begin
insert @tb select dirid,dir,dirparentid from Dir where dirid = @id
while @@rowcount > 0
insert @tb select dirid,dir,dirparentid from Dir
where dirparentid in (select dirid from @tb)
and dirid not in (select dirid from @tb)
return
end
--创建处理函数,查询指定id的所有子结点,同时包含传入的id节点。楼主可适当修改,以供自己需要。
create function f_id(@id int)
returns @re table(NodeID int,level int)
as
begin
declare @l int
set @l=0
insert @re select @id,@l
while @@rowcount>0
begin
set @l=@l+1
insert @re select a.NodeID,@l from Tree a join @re b on a.ParentNodeID=b.NodeID where b.level=@l-1
end
return
end
请教一个较难的SQL语句
表情况为
ID 姓名 上级ID
1 A 0
2 B 1
3 C 1
4 D 2
5 E 3
6 F 4
7 G 6
我现在要做的是,输入一个ID号,要能得到它自己以及所有下级的ID号,例如输入1得到1,2,3,4,5,6,7;输入2得到2,4,6,7;输入3得到3,5
create table A
(
ID int,
[姓名] varchar(10),
[上级ID] int
)
insert A
select 1,'A',0 union
select 2,'B',1 union
select 3,'C',1 union
select 4,'D',2 union
select 5,'E',3 union
select 6,'F',4 union
select 7,'G',5
go
--创建函数
create function f_nodes(@ID int)
returns varchar(8000)
as
begin
declare @tb table(ID int,[上级ID] int)
insert @tb
select ID,[上级ID] from A where ID=@ID
while @@rowcount>0
begin
insert @tb
select A.ID
,A.[上级ID]
from A
join @tb B on A.[上级ID]=B.ID
where A.ID not in(select ID from @tb)
end
declare @str varchar(8000)
set @str=''
select @str=@str+','+convert(varchar,[ID]) from @tb
return stuff(@str,1,1,'')
end
go