34,575
社区成员
发帖
与我相关
我的任务
分享
CREATE TABLE [dbo].[boms] (
picID varchar(20), --父ID
piID varchar(20) --包含的子ID
)
GO
insert into boms(piID,picID) values('001','0011')
insert into boms(piID,picID) values('001','0012')
insert into boms(piID,picID) values('0012','0013')
insert into boms(piID,picID) values('0012','0014')
insert into boms(piID,picID) values('0013','00133')
insert into boms(piID,picID) values('0013','00134')
insert into boms(piID,picID) values('00133','001331')
insert into boms(piID,picID) values('00133','001332')
go
/*
标题:查询所有节点及其所有子节点的函数
作者:爱新觉罗·毓华(十八年风雨,守得冰山雪莲花开)
时间:2009-04-12
地点:广东深圳
*/
--生成测试数据
create table tb(id varchar(10),pid varchar(10))
insert into tb select 'a', null
insert into tb select 'b', 'a'
insert into tb select 'c', 'a'
insert into tb select 'd', 'b'
insert into tb select 'e', 'b'
insert into tb select 'f', 'c'
insert into tb select 'g', 'c'
go
--创建用户定义函数
create function f_getchild(@id varchar(10)) returns varchar(8000)
as
begin
declare @i int , @ret varchar(8000)
declare @t table(id varchar(10) , pid varchar(10) , level int)
set @i = 1
insert into @t select id , pid , @i from tb where id = @id
while @@rowcount <> 0
begin
set @i = @i + 1
insert into @t select a.id , a.pid , @i from tb a , @t b where a.pid = b.id and b.level = @i - 1
end
select @ret = isnull(@ret , '') + id + ',' from @t
return left(@ret , len(@ret) - 1)
end
go
--执行查询
select id , children = isnull(dbo.f_getchild(id) , '') from tb group by id
go
--输出结果
/*
id children
---------- -------------
a a,b,c,d,e,f,g
b b,d,e
c c,f,g
d d
e e
f f
g g
(所影响的行数为 7 行)
*/
--删除测试数据
drop function f_getchild
drop table tb
CREATE TABLE [dbo].[boms] (
picID varchar(20), --父ID
piID varchar(20) --包含的子ID
)
GO
insert into boms(piID,picID) values('001','0011')
insert into boms(piID,picID) values('001','0012')
insert into boms(piID,picID) values('0012','0013')
insert into boms(piID,picID) values('0012','0014')
insert into boms(piID,picID) values('0013','00133')
insert into boms(piID,picID) values('0013','00134')
insert into boms(piID,picID) values('00133','001331')
insert into boms(piID,picID) values('00133','001332')
go
;with cte as(
select * from boms where piID in('001','0013')
union all
select a.* from boms a inner join cte b on a.piID=b.picID
)select distinct piID,picID from cte
/*
piID picID
-------------------- --------------------
001 0011
001 0012
0012 0013
0012 0014
0013 00133
0013 00134
00133 001331
00133 001332
(8 行受影响)
*/
-->Title:Generating test data
-->Author:wufeng4552
-->Date :2009-09-30 08:52:38
set nocount on
if object_id('tb','U')is not null drop table tb
go
create table tb(ID int, ParentID int)
insert into tb select 1,0
insert into tb select 2,1
insert into tb select 3,1
insert into tb select 4,2
insert into tb select 5,3
insert into tb select 6,5
insert into tb select 7,6
-->Title:查找指定節點下的子結點
if object_id('Uf_GetChildID')is not null drop function Uf_GetChildID
go
create function Uf_GetChildID(@ParentID int)
returns @t table(ID int)
as
begin
insert @t select ID from tb where ParentID=@ParentID
while @@rowcount<>0
begin
insert @t select a.ID from tb a inner join @t b
on a.ParentID=b.id and
not exists(select 1 from @t where id=a.id)
end
return
end
go
select * from dbo.Uf_GetChildID(5)
/*
ID
-----------
6
7
*/
-->Title:查找指定節點的所有父結點
if object_id('Uf_GetParentID')is not null drop function Uf_GetParentID
go
create function Uf_GetParentID(@ID int)
returns @t table(ParentID int)
as
begin
insert @t select ParentID from tb where ID=@ID
while @@rowcount!=0
begin
insert @t select a.ParentID from tb a inner join @t b
on a.id=b.ParentID and
not exists(select 1 from @t where ParentID=a.ParentID)
end
return
end
go
select * from dbo.Uf_GetParentID(2)
/*
ParentID
-----------
1
0
*/