递归限制32层以内,用循环比较好,参看:
--树形数据查询示例
--作者: 邹建if exists (select * from dbo.sysobjects where id = object_id(N'[tb]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [tb]
GO--示例数据
create table [tb]([id] int identity(1,1),[pid] int,name varchar(20))
insert [tb] select 0,'中国'
union all select 0,'美国'
union all select 0,'加拿大'
union all select 1,'北京'
union all select 1,'上海'
union all select 1,'江苏'
union all select 6,'苏州'
union all select 7,'常熟'
union all select 6,'南京'
union all select 6,'无锡'
union all select 2,'纽约'
union all select 2,'旧金山'
goif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_id]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_id]
GO/*--树形数据处理 级别及排序字段--邹建 2003-12(引用请保留此信息)--*//*--调用示例 --调用函数实现分级显示
select replicate('-',b.[level]*4)+a.name
from [tb] a,f_id()b
where a.[id]=b.[id]
order by b.sid
--*/
create function f_id()
returns @re table([id] int,[level] int,sid varchar(8000))
as
begin
declare @l int
set @l=0
insert @re select [id],@l,right(10000+[id],4)
from [tb] where [pid]=0
while @@rowcount>0
begin
set @l=@l+1
insert @re select a.[id],@l,b.sid+right(10000+a.[id],4)
from [tb] a,@re b
where a.[pid]=b.[id] and b.[level]=@l-1
end
return
end
go--调用函数实现分级显示
select replicate('-',b.[level]*4)+a.name
from [tb] a,f_id()b
where a.[id]=b.[id]
order by b.sid
go
--删除测试
drop table [tb]
drop function f_id
go/*--结果
中国
----北京
----上海
----江苏
--------苏州
------------常熟
--------南京
--------无锡
美国
----纽约
----旧金山
加拿大--*/
--树形数据查询示例
--作者: 邹建if exists (select * from dbo.sysobjects where id = object_id(N'[tb]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [tb]
GO--示例数据
create table [tb]([id] int identity(1,1),[pid] int,name varchar(20))
insert [tb] select 0,'中国'
union all select 0,'美国'
union all select 0,'加拿大'
union all select 1,'北京'
union all select 1,'上海'
union all select 1,'江苏'
union all select 6,'苏州'
union all select 7,'常熟'
union all select 6,'南京'
union all select 6,'无锡'
union all select 2,'纽约'
union all select 2,'旧金山'
goif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_id]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_id]
GO/*--树形数据处理 级别及排序字段--邹建 2003-12(引用请保留此信息)--*//*--调用示例 --调用函数实现分级显示
select replicate('-',b.[level]*4)+a.name
from [tb] a,f_id()b
where a.[id]=b.[id]
order by b.sid
--*/
create function f_id()
returns @re table([id] int,[level] int,sid varchar(8000))
as
begin
declare @l int
set @l=0
insert @re select [id],@l,right(10000+[id],4)
from [tb] where [pid]=0
while @@rowcount>0
begin
set @l=@l+1
insert @re select a.[id],@l,b.sid+right(10000+a.[id],4)
from [tb] a,@re b
where a.[pid]=b.[id] and b.[level]=@l-1
end
return
end
go--调用函数实现分级显示
select replicate('-',b.[level]*4)+a.name
from [tb] a,f_id()b
where a.[id]=b.[id]
order by b.sid
go
--删除测试
drop table [tb]
drop function f_id
go/*--结果
中国
----北京
----上海
----江苏
--------苏州
------------常熟
--------南京
--------无锡
美国
----纽约
----旧金山
加拿大--*/
不知引用那位
*/
--建立測試環境
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tCore_Dept]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tCore_Dept]
GOCREATE TABLE [dbo].[tCore_Dept] (
[DeptID] [varchar] (8) COLLATE Chinese_Taiwan_Stroke_CI_AS NOT NULL ,
[DeptName] [varchar] (80) COLLATE Chinese_Taiwan_Stroke_CI_AS NOT NULL ,
[SuperiorDeptID] [varchar] (8) COLLATE Chinese_Taiwan_Stroke_CI_AS NOT NULL
) ON [PRIMARY]
GO/*
--相應字段對應數據
A A Root
AA AA A
AB AB A
ABA ABA AB
ABAA ABAA ABA
B B Root
BA BA B
C C Root
*/
*/
CREATE Procedure GetNextDeptTree
(@Child_node varchar(8))
--WITH ENCRYPTION
As
begin
SET NOCOUNT ON
DECLARE @Cnt int
Declare @i int
Declare @tmpnode varchar(8)create table #stack (node varchar(8))
create table #stackTmp (node varchar(8))
create table #stackTmpXXX (node varchar(8))insert into #stack
select DeptID from GetNextDept(@Child_node)insert into #stackTmp
select DeptID from GetNextDept(@Child_node)select @tmpnode = @Child_node
select @Cnt = count(*) from tCore_Dept
select @i = 0loops:select * from #stack
declare cur cursor for
select node from #stackTmp
open cur
fetch next from cur into @tmpnode
while @@FETCH_STATUS = 0
begin
if(ltrim(@tmpnode)='')
print 'NULL'
else
print @tmpnode
insert into #stack select DeptID from GetNextDept(@tmpnode)
insert into #stackTmpXXX select DeptID from GetNextDept(@tmpnode)
select @i = @i + 1
fetch next from cur into @tmpnode
end
CLOSE cur
DEALLOCATE curselect @i = @i + 1delete from #stackTmp
insert into #stackTmp select node from #stackTmpXXX
delete from #stackTmpXXXif (@i >= @Cnt * 1.5 )
goto endsgoto loopsends:
select node from #stack
end
GO
(id int,CoustomID int)insert t values (1,2)
insert t values (2,3)
insert t values (58,12)
insert t values (5,6)
insert t values (12,23)
insert t values (3,9)go
create function f_xun(@id int)
returns varchar(200)
as
begin
declare @t table(id int,CoustomID int,level int)
declare @i int,@re varchar(200)
set @i=1 set @i=1
set @re=''
insert into @t
select id,CoustomID,@i from t where Coustomid=@id while @@rowcount<>0
begin
set @i=@i+1 insert into @t
select a.id,a.CoustomID,@i
from t a,@t b
where a.Coustomid=b.id and b.level=@i-1
end
select @re=@re+','+cast(id as varchar) from @t order by id asc
return(stuff(@re+','+cast(@id as varchar),1,1,''))
end
goselect id,Coustomid=dbo.f_xun(Coustomid) from t where Coustomid=9drop function f_xun
drop table tid Coustomid
-------------- ----------------------------------------------
3 1,2,3,9(所影响的行数为 1 行)
(
ID int,
CoustomID int
)
insert A
select 1, 2 union
select 2, 3 union
select 58, 12 union
select 5, 6 union
select 12, 23 union
select 3, 9
go--创建函数
create function f_str(@CoustomID int)
returns varchar(8000)
as
begin
declare @tb table(num int identity,CoustomID int)
insert @tb(CoustomID)
select distinct @CoustomID from A where exists(select 1 from A where CoustomID=@CoustomID) while @@rowcount>0
begin
insert @tb(CoustomID)
select A.ID
from A
join @tb B on A.CoustomID=B.CoustomID
where not exists(select 1 from @tb where CoustomID=A.ID)
end declare @str varchar(8000)
select @str=''
select @str=@str+','+convert(varchar,CoustomID) from @tb order by num desc
return stuff(@str,1,1,'')
end
go--查询
select dbo.f_str(9)--删除测试环境
drop function f_str
drop table A--结果
/*
1,2,3,9(所影响的行数为 1 行)
*/
create table a (id int,zid int) -----建立测试数据
insert into a select 1,2
insert into a select 2,3
insert into a select 58,12
insert into a select 5,6
insert into a select 12,23
insert into a select 3,9create function xy(@x int,@y int)---建立函数
returns int
as
begin
declare
@a int
while exists(select 1 from a where zid=@y)
begin
select @a=id from a where zid=@y
if @a=@x
begin
return 1
end
else
set @y=@a
end
return 0
endselect * -----查询
from (select *
from (select id
from a) a ,(select zid from a) b
where dbo.xy(a.id,b.zid)=1 ) cc
where id=1
drop table a ---销毁测试数据
drop function xy
(
ID int,
CoustomID int
)
insert A
select 1, 2 union
select 2, 3 union
select 58, 12 union
select 5, 6 union
select 12, 23 union
select 3, 9
go--创建函数
create function f_str(@CoustomID int)
returns varchar(8000)
as
begin
declare @tb table(num int identity,CoustomID int)
insert @tb(CoustomID)
select distinct @CoustomID from A where exists(select 1 from A where CoustomID=@CoustomID) while @@rowcount>0
begin
insert @tb(CoustomID)
select A.ID
from A
join @tb B on A.CoustomID=B.CoustomID
where not exists(select 1 from @tb where CoustomID=A.ID)
end declare @str varchar(8000)
select @str=''
select @str=@str+','+convert(varchar,CoustomID) from @tb order by num desc
return stuff(@str,1,1,'')
end
go--查询
select dbo.f_str(9)--删除测试环境
drop function f_str
drop table A