34,576
社区成员
发帖
与我相关
我的任务
分享
create table AAA(ID INT,strNum1 varchar(8),strNum2 varchar(8),intTaxis int)
insert into AAA select 1,'01' ,'001',1
insert into AAA select 2,'001','011',2
insert into AAA select 3,'001','010',1
insert into AAA select 6,'01' ,'002',2
insert into AAA select 4,'002','021',1
insert into AAA select 5,'002','022',2
go
--创建用户定义函数
create function f_getChild(@strNum1 VARCHAR(10))
returns @t table(strNum1 VARCHAR(10),strNum2 VARCHAR(10),Level INT,Ord varchar(100))
as
begin
declare @i int
set @i=1
insert into @t select strNum1,strNum2,@i,right('0'+strNum2,3) from AAA where strNum1 = @strNum1
while @@rowcount<>0
begin
set @i=@i+1
insert into @t
select
a.strNum1,a.strNum2,@i,b.Ord+a.strNum2
from
AAA a,@t b
where
a.strNum1=b.strNum2 and b.Level=@i-1
and
not exists(select 1 from @t where strNum1=a.strNum1 and strNum2=a.strNum2)
end
return
end
go
--执行查询
select * from dbo.f_getChild('01') order by Ord
go
--输出结果
/*
strNum1 strNum2 Level Ord
---------- ---------- ----------- -----------
01 001 1 001
001 010 2 001010
001 011 2 001011
01 002 1 002
002 021 2 002021
002 022 2 002022
*/
--删除测试数据
drop function f_getChild
drop table AAA
go
Declare @Id Int
Set @Id = 5; ---在此修改父节点
With RootNodeCTE(Id,ParentId)
As
(
Select Id,ParentId From BOM Where ParentId In (@Id)
Union All
Select BOM.Id,BOM.ParentId From RootNodeCTE
Inner Join BOM
On RootNodeCTE.Id = BOM.ParentId
)
Select * From RootNodeCTE
--生成测试数据
create table BOM(ID INT,PID INT,MSG VARCHAR(1000))
insert into BOM select 1,0,NULL
insert into BOM select 2,1,NULL
insert into BOM select 3,1,NULL
insert into BOM select 4,2,NULL
insert into BOM select 5,3,NULL
insert into BOM select 6,5,NULL
insert into BOM select 7,6,NULL
go
--创建用户定义函数
create function f_getChild(@ID VARCHAR(10))
returns @t table(ID VARCHAR(10),PID VARCHAR(10),Level INT)
as
begin
declare @i int,@ret varchar(8000)
set @i = 1
insert into @t select ID,PID,@i from BOM where PID = @ID
while @@rowcount<>0
begin
set @i = @i + 1
insert into @t
select
a.ID,a.PID,@i
from
BOM a,@t b
where
a.PID=b.ID and b.Level = @i-1
end
return
end
go
--执行查询
select ID from dbo.f_getChild(3)
go
--输出结果
/*
5
6
7
*/
--删除测试数据
drop function f_getChild
drop table BOM