--自定义函数,返回指定目录下的所有子目录列表 create function f_child(@ClassID int) returns @re table(ClassID int,Level int) as begin declare @l int set @l=0 insert @re select @ClassID,@l while @@rowcount>0 begin set @l=@l+1 insert @re select a.ClassID,@l from 表 a join @re b on a.ClassParentID=b.ClassID where b.level=@l end return end go--调用函数实现查询 select a.* from 表 a join dbo.f_child(1) b on a.ClassID=b.ClassID go
來自zjcxc --测试数据 CREATE TABLE [dbo].[地方管理] ( [地名] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL , [编号] [numeric](18, 0) NULL , [上级地名] [numeric](18, 0) NULL , [直接管辖地名] [numeric](18, 0) NULL ) ON [PRIMARY]insert into 地方管理 select '中国',001,0,0 union all select '北京', 002,001,001 union all select '广东', 003,001,001 union all select '上海', 004,001,001 union all select '广州', 005,003,003 union all select '佛山', 006,003,001 union all select '天河区', 007,005,005 union all select '天河路', 008,007,001 union all select '白云区', 009,005,005 union all select '天河一路', 010,008,001 union all select '天河二路', 008,001,003 union all select '南海', 012,006,001 union all select '顺德', 013,006,006 union all select '大历', 014,012,001 union all select '桂城', 015,012,006 go--创建一个函数,得到指定编号的所有子编号 create function f_child(@编号 numeric(18,0)) returns @re table(编号 numeric(18,0),level int) as begin declare @l int set @l=0 insert @re values(@编号,@l) while @@rowcount>0 begin set @l=@l+1 insert @re select a.编号,@l from 地方管理 a join @re b on a.上级地名=b.编号 where b.level=@l-1 end return end go--调用实现查询 select a.* from 地方管理 a join dbo.f_child(001) b on a.编号=b.编号 go--删除测试 drop table 地方管理 drop function f_child/*--测试结果(所影响的行数为 15 行)地名 编号 上级地名 直接管辖地名 ------- ------ ------- ------------------ 中国 1 0 0 北京 2 1 1 广东 3 1 1 上海 4 1 1 广州 5 3 3 佛山 6 3 1 天河区 7 5 5 天河路 8 7 1 天河路 8 7 1 白云区 9 5 5 天河一路 10 8 1 天河一路 10 8 1 天河二路 8 1 3 天河二路 8 1 3 南海 12 6 1 顺德 13 6 6 大历 14 12 1 桂城 15 12 6(所影响的行数为 18 行) --*/
--上面写错了一点,改一下:--自定义函数,返回指定目录下的所有子目录列表 create function f_child(@ClassID int) returns @re table(ClassID int,Level int) as begin declare @l int set @l=0 insert @re select @ClassID,@l while @@rowcount>0 and @l<6 begin set @l=@l+1 insert @re select a.ClassID,@l from 表 a join @re b on a.ClassParentID=b.ClassID where b.level=@l-1 end return end go--调用函数实现查询 select a.* from 表 a join dbo.f_child(1) b on a.ClassID=b.ClassID go
--测试--测试数据 create table 表(ClassID int,ClassParentID int) insert 表 select 1,0 union all select 2,1 union all select 3,2 union all select 4,3 union all select 5,3 go--自定义函数,返回指定目录下的所有子目录列表 create function f_child(@ClassID int) returns @re table(ClassID int,Level int) as begin declare @l int set @l=0 insert @re select @ClassID,@l while @@rowcount>0 and @l<6 begin set @l=@l+1 insert @re select a.ClassID,@l from 表 a join @re b on a.ClassParentID=b.ClassID where b.level=@l-1 end return end go--调用函数实现查询 select a.* from 表 a join dbo.f_child(1) b on a.ClassID=b.ClassID go--加上缩进效果 select ClassID=space(b.level*4)+cast(a.ClassID as varchar),a.ClassParentID from 表 a join dbo.f_child(1) b on a.ClassID=b.ClassIDgo --删除测试环境 drop table 表 drop function f_child/*--测试结果 ClassID ClassParentID ----------- ------------- 1 0 2 1 3 2 4 3 5 3(所影响的行数为 5 行)ClassID ClassParentID -------------- ---------------- 10 2 1 3 2 4 3 5 3(所影响的行数为 5 行) --*/
--上面我写的函数错了一点,改一下:--自定义函数,返回指定目录下的所有子目录列表 create function f_child(@ClassID int) returns @re table(ClassID int,Level int) as begin declare @l int set @l=0 insert @re select @ClassID,@l while @@rowcount>0 --and @l<6 这个是多余的 begin set @l=@l+1 insert @re select a.ClassID,@l from 表 a join @re b on a.ClassParentID=b.ClassID where b.level=@l-1 end return end go--调用函数实现查询 select a.* from 表 a join dbo.f_child(1) b on a.ClassID=b.ClassID go
create function f_child(@ClassID int)
returns @re table(ClassID int,Level int)
as
begin
declare @l int
set @l=0
insert @re select @ClassID,@l
while @@rowcount>0
begin
set @l=@l+1
insert @re select a.ClassID,@l
from 表 a join @re b on a.ClassParentID=b.ClassID
where b.level=@l
end
return
end
go--调用函数实现查询
select a.* from 表 a join dbo.f_child(1) b on a.ClassID=b.ClassID
go
--测试数据
CREATE TABLE [dbo].[地方管理] (
[地名] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[编号] [numeric](18, 0) NULL ,
[上级地名] [numeric](18, 0) NULL ,
[直接管辖地名] [numeric](18, 0) NULL
) ON [PRIMARY]insert into 地方管理
select '中国',001,0,0
union all select '北京', 002,001,001
union all select '广东', 003,001,001
union all select '上海', 004,001,001
union all select '广州', 005,003,003
union all select '佛山', 006,003,001
union all select '天河区', 007,005,005
union all select '天河路', 008,007,001
union all select '白云区', 009,005,005
union all select '天河一路', 010,008,001
union all select '天河二路', 008,001,003
union all select '南海', 012,006,001
union all select '顺德', 013,006,006
union all select '大历', 014,012,001
union all select '桂城', 015,012,006
go--创建一个函数,得到指定编号的所有子编号
create function f_child(@编号 numeric(18,0))
returns @re table(编号 numeric(18,0),level int)
as
begin
declare @l int
set @l=0
insert @re values(@编号,@l)
while @@rowcount>0
begin
set @l=@l+1
insert @re select a.编号,@l
from 地方管理 a join @re b on a.上级地名=b.编号
where b.level=@l-1
end
return
end
go--调用实现查询
select a.* from 地方管理 a join dbo.f_child(001) b on a.编号=b.编号
go--删除测试
drop table 地方管理
drop function f_child/*--测试结果(所影响的行数为 15 行)地名 编号 上级地名 直接管辖地名
------- ------ ------- ------------------
中国 1 0 0
北京 2 1 1
广东 3 1 1
上海 4 1 1
广州 5 3 3
佛山 6 3 1
天河区 7 5 5
天河路 8 7 1
天河路 8 7 1
白云区 9 5 5
天河一路 10 8 1
天河一路 10 8 1
天河二路 8 1 3
天河二路 8 1 3
南海 12 6 1
顺德 13 6 6
大历 14 12 1
桂城 15 12 6(所影响的行数为 18 行)
--*/
create function f_child(@ClassID int)
returns @re table(ClassID int,Level int)
as
begin
declare @l int
set @l=0
insert @re select @ClassID,@l
while @@rowcount>0 and @l<6
begin
set @l=@l+1
insert @re select a.ClassID,@l
from 表 a join @re b on a.ClassParentID=b.ClassID
where b.level=@l-1
end
return
end
go--调用函数实现查询
select a.* from 表 a join dbo.f_child(1) b on a.ClassID=b.ClassID
go
create table 表(ClassID int,ClassParentID int)
insert 表 select 1,0
union all select 2,1
union all select 3,2
union all select 4,3
union all select 5,3
go--自定义函数,返回指定目录下的所有子目录列表
create function f_child(@ClassID int)
returns @re table(ClassID int,Level int)
as
begin
declare @l int
set @l=0
insert @re select @ClassID,@l
while @@rowcount>0 and @l<6
begin
set @l=@l+1
insert @re select a.ClassID,@l
from 表 a join @re b on a.ClassParentID=b.ClassID
where b.level=@l-1
end
return
end
go--调用函数实现查询
select a.* from 表 a join dbo.f_child(1) b on a.ClassID=b.ClassID
go--加上缩进效果
select ClassID=space(b.level*4)+cast(a.ClassID as varchar),a.ClassParentID
from 表 a join dbo.f_child(1) b on a.ClassID=b.ClassIDgo
--删除测试环境
drop table 表
drop function f_child/*--测试结果
ClassID ClassParentID
----------- -------------
1 0
2 1
3 2
4 3
5 3(所影响的行数为 5 行)ClassID ClassParentID
-------------- ----------------
10
2 1
3 2
4 3
5 3(所影响的行数为 5 行)
--*/
create function f_child(@ClassID int)
returns @re table(ClassID int,Level int)
as
begin
declare @l int
set @l=0
insert @re select @ClassID,@l
while @@rowcount>0 --and @l<6 这个是多余的
begin
set @l=@l+1
insert @re select a.ClassID,@l
from 表 a join @re b on a.ClassParentID=b.ClassID
where b.level=@l-1
end
return
end
go--调用函数实现查询
select a.* from 表 a join dbo.f_child(1) b on a.ClassID=b.ClassID
go