if not object_id('Tempdb..#T') is null drop table #T Go Create table #T([classid] int,[lpclassid] int) Insert #T select 1,2 union all select 2,3 union all select 3,0 Go declare @classid int set @classid=2 ;with c as (select * from #T where [classid]=@classid union all select a.* from #T a join C on c.[lpclassid]=a.[classid]) select * from C (3 行受影响) classid lpclassid ----------- ----------- 2 3 3 0(2 行受影响)
2000时这样用--> --> (Roy)生成測試數據
if not object_id('Tempdb..#T') is null drop table #T Go Create table #T([classid] int,[lpclassid] int) Insert #T select 1,2 union all select 2,3 union all select 3,0 Go declare @T table([classid] int,[lpclassid] int)declare @classid int set @classid=2 while exists(select 1 from #T where [classid]=@classid) begin insert @T select * from #T where [classid]=@classid set @classid=(select top 1 [lpclassid] from #T where [classid]=@classid) end select * from @T
--2005化解32层递归限制,2000可用循环替代或用(游标while加break递归自己用一个变量传参)if object_id('Tree','U') is not null drop table [Tree] go CREATE TABLE [dbo].[Tree]( [ID] [bigint] identity, [Parent] as (ID-1), [Name] as ('Name'+rtrim(ID)) ) go declare @i int set @i=35 while @i>0 begin insert [tree] default values set @i=@i-1 end --生成格式: /**//* ID Parent Name -------------------- -------------------- ---------------------------- 1 0 Name1 2 1 Name2 3 2 Name3 4 3 Name4 5 4 Name5 6 5 Name6 7 6 Name7 8 7 Name8 9 8 Name9 10 9 Name10 ................................................................................................ 31 30 Name31 32 31 Name32 33 32 Name33 34 33 Name34 35 34 Name35*/ go if object_id('F_BOM','FN') is not null drop function F_BOM go create function F_BOM(@ID int) returns nvarchar(1000) as begin declare @s nvarchar(1000),@Name nvarchar(20) lab: set @Name =(select Name from Tree where ID=@ID) select @ID=Parent from Tree where ID=@ID if @Name is not null begin set @s=@Name+isnull('-'+@s,'') goto lab end return @s end go if object_id('F_BOM2','FN') is not null drop function F_BOM2 go create function F_BOM2(@ID int) returns nvarchar(1000) as begin declare @s nvarchar(1000) while exists(select 1 from Tree where ID=@ID) select @s=Name+isnull('-'+@s,''),@ID=Parent from Tree where ID=@ID return @s end go--SQL2005:if object_id('F_BOM3','FN') is not null drop function F_BOM3 go create function F_BOM3(@ID int) returns nvarchar(max) as begin declare @s nvarchar(max); with BOM(ID,Name,parent,lev) as ( select ID,cast(Name as nvarchar(max)),parent,0 from tree where ID=@ID union all select a.ID,cast(a.Name+'-'+b.Name as nvarchar(max)),a.Parent,b.lev+1 from Tree a join BOM b on a.ID=b.Parent ) select @s=Name from BOM where lev=(select max(lev) from BOM) option(maxrecursion 0) -----------設置遞歸次數0為無限制,默認為100層 return @s end goselect dbo.F_BOM(35) select dbo.F_BOM2(35) select dbo.F_BOM3(35) /**//* Name1-Name2-Name3-Name4-Name5-Name6-Name7-Name8-Name9-Name10- Name11-Name12-Name13-Name14-Name15-Name16-Name17-Name18-Name19-Name20- Name21-Name22-Name23-Name24-Name25-Name26-Name27-Name28-Name29-Name30- Name31-Name32-Name33-Name34-Name35 */
if not object_id('Tempdb..#T') is null
drop table #T
Go
Create table #T([classid] int,[lpclassid] int)
Insert #T
select 1,2 union all
select 2,3 union all
select 3,0
Go
declare @classid int
set @classid=2
;with c
as
(select * from #T where [classid]=@classid
union all
select a.* from #T a join C on c.[lpclassid]=a.[classid])
select * from C
(3 行受影响)
classid lpclassid
----------- -----------
2 3
3 0(2 行受影响)
if not object_id('Tempdb..#T') is null
drop table #T
Go
Create table #T([classid] int,[lpclassid] int)
Insert #T
select 1,2 union all
select 2,3 union all
select 3,0
Go
declare @T table([classid] int,[lpclassid] int)declare @classid int
set @classid=2
while exists(select 1 from #T where [classid]=@classid)
begin
insert @T select * from #T where [classid]=@classid
set @classid=(select top 1 [lpclassid] from #T where [classid]=@classid)
end
select * from @T
drop table [Tree]
go
CREATE TABLE [dbo].[Tree](
[ID] [bigint] identity,
[Parent] as (ID-1),
[Name] as ('Name'+rtrim(ID))
)
go
declare @i int
set @i=35
while @i>0
begin
insert [tree] default values
set @i=@i-1
end
--生成格式:
/**//*
ID Parent Name
-------------------- -------------------- ----------------------------
1 0 Name1
2 1 Name2
3 2 Name3
4 3 Name4
5 4 Name5
6 5 Name6
7 6 Name7
8 7 Name8
9 8 Name9
10 9 Name10
................................................................................................
31 30 Name31
32 31 Name32
33 32 Name33
34 33 Name34
35 34 Name35*/
go
if object_id('F_BOM','FN') is not null
drop function F_BOM
go
create function F_BOM(@ID int)
returns nvarchar(1000)
as
begin
declare @s nvarchar(1000),@Name nvarchar(20)
lab:
set @Name =(select Name from Tree where ID=@ID)
select @ID=Parent from Tree where ID=@ID
if @Name is not null
begin
set @s=@Name+isnull('-'+@s,'')
goto lab
end
return @s
end
go
if object_id('F_BOM2','FN') is not null
drop function F_BOM2
go
create function F_BOM2(@ID int)
returns nvarchar(1000)
as
begin
declare @s nvarchar(1000)
while exists(select 1 from Tree where ID=@ID)
select @s=Name+isnull('-'+@s,''),@ID=Parent from Tree where ID=@ID
return @s
end
go--SQL2005:if object_id('F_BOM3','FN') is not null
drop function F_BOM3
go
create function F_BOM3(@ID int)
returns nvarchar(max)
as
begin
declare @s nvarchar(max);
with BOM(ID,Name,parent,lev)
as
(
select ID,cast(Name as nvarchar(max)),parent,0 from tree where ID=@ID
union all
select
a.ID,cast(a.Name+'-'+b.Name as nvarchar(max)),a.Parent,b.lev+1
from
Tree a
join
BOM b on a.ID=b.Parent
)
select @s=Name from BOM where lev=(select max(lev) from BOM)
option(maxrecursion 0) -----------設置遞歸次數0為無限制,默認為100層
return @s
end
goselect dbo.F_BOM(35)
select dbo.F_BOM2(35)
select dbo.F_BOM3(35)
/**//*
Name1-Name2-Name3-Name4-Name5-Name6-Name7-Name8-Name9-Name10-
Name11-Name12-Name13-Name14-Name15-Name16-Name17-Name18-Name19-Name20-
Name21-Name22-Name23-Name24-Name25-Name26-Name27-Name28-Name29-Name30-
Name31-Name32-Name33-Name34-Name35
*/