-----------------树型例子-----------------create table t1
(
id int identity(1,1),
code int,
parentcode int
)
go
insert t1 select 1,null
union all select 2,1
union all select 3,1
union all select 4,2--drop table t1
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_cid]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_cid]
GO/*--树形数据处理 查询指定id的所有子--邹建 2003-12(引用请保留此信息)--*//*--调用示例 --调用(查询所有的子)
select a.*,层次=b.[level] from [tb] a,f_cid(2)b where a.code=b.code
--*/
create function f_cid(
@id int
)returns @re table(code int,[level] int)
as
begin
declare @l int
set @l=0
insert @re select @id,@l
while @@rowcount>0
begin
set @l=@l+1
insert @re select a.code,@l
from [t1] a,@re b
where a.parentcode=b.code and b.[level]=@l-1
end
return
end
go--调用(查询所有的子)
select a.*,层次=b.[level] from [t1] a,f_cid(2)b where a.code=b.code
go
(
id int identity(1,1),
code int,
parentcode int
)
go
insert t1 select 1,null
union all select 2,1
union all select 3,1
union all select 4,2--drop table t1
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_cid]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_cid]
GO/*--树形数据处理 查询指定id的所有子--邹建 2003-12(引用请保留此信息)--*//*--调用示例 --调用(查询所有的子)
select a.*,层次=b.[level] from [tb] a,f_cid(2)b where a.code=b.code
--*/
create function f_cid(
@id int
)returns @re table(code int,[level] int)
as
begin
declare @l int
set @l=0
insert @re select @id,@l
while @@rowcount>0
begin
set @l=@l+1
insert @re select a.code,@l
from [t1] a,@re b
where a.parentcode=b.code and b.[level]=@l-1
end
return
end
go--调用(查询所有的子)
select a.*,层次=b.[level] from [t1] a,f_cid(2)b where a.code=b.code
go
----------------------------------------------------------------
create function f_getpk(@my_pk int)
returns varchar(8000)
as
begin
declare @ret varchar(8000),@var1 varchar(1000),@var2 varchar(1000)
select
@ret = rtrim(my_pk),
@var1 = rtrim(my_pk),
@var2 = ''
from
mytable
where
my_pk = @my_pk while (@var1 != '')
begin
while charindex(',',@var1)>0
begin
select
@ret = @ret + ',' + rtrim(my_pk),
@var2 = @var2 + ',' + rtrim(my_pk)
from
mytable
where
super_pk = left(@var1,charindex(',',@var1)-1)
set @var1 = stuff(@var1,1,charindex(',',@var1),'')
end
select
@ret = @ret + ',' + rtrim(my_pk),
@var2 = @var2 + ',' + rtrim(my_pk)
from
mytable
where
super_pk = @var1
set @var1 = stuff(@var2,1,1,'')
set @var2 = ''
end
return @ret
end
RETURNS @rt_table table(
area_id int,
area_name varchar (50)
)
AS
BEGIN
declare @tep_name varchar(50),
@tep_id int,
@sub_count int
declare c1 cursor for
(
select my_pk,[name] from mytable where super_pk = @area_id
)
open c1
fetch c1 into @tep_id,@tep_name
select @sub_count = 0
while @@fetch_status>=0
begin
select @sub_count=count(*) from mytable where super_pk = @tep_id
insert into @rt_table(area_id,area_name) values(@tep_id,@tep_name)
if @sub_count>0
begin
insert into @rt_table(area_id,area_name)
select area_id,area_name from dbo.FUNC_GET_SUBAREA(@tep_id)
end
FETCH NEXT FROM c1 INTO @tep_id,@tep_name
end
close c1
deallocate c1
return
END
//使用: select * from dbo.FUNC_GET_SUBAREA(2) order by 1
//使用: select * from dbo.FUNC_GET_SUBAREA(1) order by 1
//函数如下::CREATE FUNCTION dbo.FUNC_GET_SUBAREA(@area_id int,@level_num int =null)
RETURNS @rt_table table(
area_id int,
area_name varchar (50)
)
AS
BEGIN
declare @tep_name varchar(50),
@tep_id int,
@sub_count int
declare c1 cursor for
(
select my_pk,[name] from mytable where super_pk = @area_id
)
if @level_num=0
begin
insert into @rt_table(area_id,area_name)
select my_pk,[name] from mytable where my_pk = @area_id
end
open c1
fetch c1 into @tep_id,@tep_name
select @sub_count = 0
while @@fetch_status>=0
begin
select @sub_count=count(*) from mytable where super_pk = @tep_id
insert into @rt_table(area_id,area_name) values(@tep_id,@tep_name)
if @sub_count>0
begin
insert into @rt_table(area_id,area_name)
select area_id,area_name from dbo.FUNC_GET_SUBAREA(@tep_id,1)
end
FETCH NEXT FROM c1 INTO @tep_id,@tep_name
end
close c1
deallocate c1
return
END
//使用: select * from dbo.FUNC_GET_SUBAREA(2,0) order by 1
//使用: select * from dbo.FUNC_GET_SUBAREA(1,0) order by 1