For example: --生成测试数据 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
Another one:--生成测试数据 create table BOM(ID VARCHAR(10),PID VARCHAR(10)) insert into BOM select 'a',NULL insert into BOM select 'b','a' insert into BOM select 'c','a' insert into BOM select 'd','b' insert into BOM select 'e','b' insert into BOM select 'f','c' insert into BOM 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 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
select @ret = isnull(@ret,'')+ID from @t
return @ret end go--执行查询 select ID,isnull(dbo.f_getChild(ID),'') from BOM group by ID go--输出结果 /* a bcdefg b de c fg d e f g */--删除测试数据 drop function f_getChild drop table BOM
----创建测试数据 declare @t1 table(id int,上级 varchar(10), 下级 varchar(10)) insert @t1 select 1,'张三','李四' union all select 2,'张三','王五' union all select 3,'赵六','李七' union all select 4,'李四','李八' union all select 5,'李八','钱九' union all select 6,'王五','陈三' union all select 7,'严五','赵四' union all select 8,'王五','马七' declare @name varchar(10) set @name = '张三' declare @t2 table(下级 varchar(10)) ----获得所有子节点 insert into @t2 select 下级 from @t1 where 上级 = @name while @@rowcount > 0 insert into @t2 select a.下级 from @t1 a,@t2 b where a.上级 = b.下级 and a.下级 not in(select 下级 from @t2) ----查询所有叶节点 select * from @t2 b where not exists(select 1 from @t1 where 上级 = b.下级)
由于我要的记录集是原表格式的,所以我改了一下但是没有查出我要的结果,请帮我看一下--删除测试数据 drop function f_getChild drop table BOM--生成测试数据 create table BOM(ID VARCHAR(10),PID VARCHAR(10)) insert into BOM select 'a',NULL insert into BOM select 'b','a' insert into BOM select 'c','a' insert into BOM select 'd','b' insert into BOM select 'e','b' insert into BOM select 'f','c' insert into BOM select 'g','c' insert into BOM select '1','g' insert into BOM select '2',NULL insert into BOM select '3','2' 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 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
select @ret = isnull(@ret,'0')+ ',' +ID from @t
return @ret end go--执行查询 select ID,isnull(dbo.f_getChild(ID),'') from BOM group by ID declare @ids varchar(1000) select @ids=dbo.f_getChild('a') select * from BOM where ID in (select @ids)
select * from BOM where ID in (select @ids) -------------^不可以这么使用吗?
/* 1。实现级联查询 例如,提供一个员工编号,能够查询出该 员工的上级和间接上级的信息。 2。实现级联查询 例如,提供一个员工编号,能够查询出该 员工的下级和间接下级的信息。 3。实现级联删除。例如,指定一个员工编号,当删除该员工 的基本信息的时候,该员工的所有下级全部被删除。无论 上级还是下级,都包括员工自己。 4。实现级联更新。当一个员工的工号改变时,所有以该员工 为直接上级的员工的reportto字段的值都变为新的工号。 */ create table emp( eid int primary key, ename varchar(20), sal money, reportto int references emp(eid) --该员工的直接上级 ) --drop table emp insert into emp select 1001,'rain',1000,null union select 1002,'ann',3000,1001 union select 1003,'lopez',2000,1001 union select 1004,'nakata',3000,1002 union select 1005,'tae',1500,1004 union select 1006,'raul',900,1004 union select 1007,'owen',15000,1006 --select * from emp /*实现级联查询 例如,提供一个员工编号,能够查询出该员工的下级和间接下级的信息。*/ create proc p2 @eid int as begin declare @re table(eid int,level int) declare @l int set @l=0 insert @re select @eid,@l while @@rowcount>0 --全局变量@@rowcount,记录上次操作影响的行数 begin set @l=@l+1 insert @re select a.eid,@l from emp as a,@re as b where a.reportto=b.eid and b.level=@l-1 --循环将当前记录的直接下级插入@re end select a.* from @re as b inner join emp as a on a.eid = b.eid end --drop proc p2 exec p2 1004
select * from BOM where ID in (select @ids)==>select * from BOM where charindex(','+rtrim(ID)+',',','+@ids+',')>0
--生成测试数据
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
create table BOM(ID VARCHAR(10),PID VARCHAR(10))
insert into BOM select 'a',NULL
insert into BOM select 'b','a'
insert into BOM select 'c','a'
insert into BOM select 'd','b'
insert into BOM select 'e','b'
insert into BOM select 'f','c'
insert into BOM 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 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
select @ret = isnull(@ret,'')+ID from @t
return @ret
end
go--执行查询
select ID,isnull(dbo.f_getChild(ID),'') from BOM group by ID
go--输出结果
/*
a bcdefg
b de
c fg
d
e
f
g
*/--删除测试数据
drop function f_getChild
drop table BOM
declare @t1 table(id int,上级 varchar(10), 下级 varchar(10))
insert @t1
select 1,'张三','李四' union all
select 2,'张三','王五' union all
select 3,'赵六','李七' union all
select 4,'李四','李八' union all
select 5,'李八','钱九' union all
select 6,'王五','陈三' union all
select 7,'严五','赵四' union all
select 8,'王五','马七'
declare @name varchar(10)
set @name = '张三'
declare @t2 table(下级 varchar(10))
----获得所有子节点
insert into @t2 select 下级 from @t1 where 上级 = @name
while @@rowcount > 0
insert into @t2 select a.下级 from @t1 a,@t2 b where a.上级 = b.下级 and a.下级 not in(select 下级 from @t2)
----查询所有叶节点
select * from @t2 b where not exists(select 1 from @t1 where 上级 = b.下级)
drop function f_getChild
drop table BOM--生成测试数据
create table BOM(ID VARCHAR(10),PID VARCHAR(10))
insert into BOM select 'a',NULL
insert into BOM select 'b','a'
insert into BOM select 'c','a'
insert into BOM select 'd','b'
insert into BOM select 'e','b'
insert into BOM select 'f','c'
insert into BOM select 'g','c'
insert into BOM select '1','g'
insert into BOM select '2',NULL
insert into BOM select '3','2'
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 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
select @ret = isnull(@ret,'0')+ ',' +ID from @t
return @ret
end
go--执行查询
select ID,isnull(dbo.f_getChild(ID),'') from BOM group by ID
declare @ids varchar(1000)
select @ids=dbo.f_getChild('a')
select * from BOM where ID in (select @ids)
-------------^不可以这么使用吗?
1。实现级联查询 例如,提供一个员工编号,能够查询出该
员工的上级和间接上级的信息。
2。实现级联查询 例如,提供一个员工编号,能够查询出该
员工的下级和间接下级的信息。
3。实现级联删除。例如,指定一个员工编号,当删除该员工
的基本信息的时候,该员工的所有下级全部被删除。无论
上级还是下级,都包括员工自己。
4。实现级联更新。当一个员工的工号改变时,所有以该员工
为直接上级的员工的reportto字段的值都变为新的工号。
*/
create table emp(
eid int primary key,
ename varchar(20),
sal money,
reportto int references emp(eid) --该员工的直接上级
)
--drop table emp
insert into emp select 1001,'rain',1000,null
union select 1002,'ann',3000,1001
union select 1003,'lopez',2000,1001
union select 1004,'nakata',3000,1002
union select 1005,'tae',1500,1004
union select 1006,'raul',900,1004
union select 1007,'owen',15000,1006
--select * from emp
/*实现级联查询 例如,提供一个员工编号,能够查询出该员工的下级和间接下级的信息。*/
create proc p2 @eid int
as
begin
declare @re table(eid int,level int)
declare @l int
set @l=0
insert @re select @eid,@l
while @@rowcount>0
--全局变量@@rowcount,记录上次操作影响的行数
begin
set @l=@l+1
insert @re select a.eid,@l from emp as a,@re as b where a.reportto=b.eid and b.level=@l-1
--循环将当前记录的直接下级插入@re
end
select a.* from @re as b inner join emp as a on a.eid = b.eid
end
--drop proc p2
exec p2 1004