表内容如下:
id1 id2 content
1 0 a
2 1 ab
3 1 ac
4 2 abc
5 2 abd
6 3 acb
7 3 acd
8 3 acc
9 4 abcb
............此表用来保存目录结构的表,记录通过id2成为与其等值id1记录的子目录。
我如何查询任一级别的目录包含的所有子目录数?
id1 id2 content
1 0 a
2 1 ab
3 1 ac
4 2 abc
5 2 abd
6 3 acb
7 3 acd
8 3 acc
9 4 abcb
............此表用来保存目录结构的表,记录通过id2成为与其等值id1记录的子目录。
我如何查询任一级别的目录包含的所有子目录数?
--生成测试数据
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
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--输出结果
/*
ID
----
5
6
7
*/--删除测试数据
drop function f_getChild
drop table BOM
标题:查询指定节点及其所有子节点的函数
作者:爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开)
时间:2008-05-12
地点:广东深圳
*/create table tb(id varchar(3) , pid varchar(3) , name varchar(10))
insert into tb values('001' , null , '广东省')
insert into tb values('002' , '001' , '广州市')
insert into tb values('003' , '001' , '深圳市')
insert into tb values('004' , '002' , '天河区')
insert into tb values('005' , '003' , '罗湖区')
insert into tb values('006' , '003' , '福田区')
insert into tb values('007' , '003' , '宝安区')
insert into tb values('008' , '007' , '西乡镇')
insert into tb values('009' , '007' , '龙华镇')
insert into tb values('010' , '007' , '松岗镇')
go--查询指定节点及其所有子节点的函数
create function f_cid(@ID varchar(3)) returns @t_level table(id varchar(3) , level int)
as
begin
declare @level int
set @level = 1
insert into @t_level select @id , @level
while @@ROWCOUNT > 0
begin
set @level = @level + 1
insert into @t_level select a.id , @level
from tb a , @t_Level b
where a.pid = b.id and b.level = @level - 1
end
return
end
go--调用函数查询001(广东省)及其所有子节点
select a.* from tb a , f_cid('001') b where a.id = b.id order by a.id
/*
id pid name
---- ---- ----------
001 NULL 广东省
002 001 广州市
003 001 深圳市
004 002 天河区
005 003 罗湖区
006 003 福田区
007 003 宝安区
008 007 西乡镇
009 007 龙华镇
010 007 松岗镇(所影响的行数为 10 行)
*/--调用函数查询002(广州市)及其所有子节点
select a.* from tb a , f_cid('002') b where a.id = b.id order by a.id
/*
id pid name
---- ---- ----------
002 001 广州市
004 002 天河区(所影响的行数为 2 行)
*/--调用函数查询003(深圳市)及其所有子节点
select a.* from tb a , f_cid('003') b where a.id = b.id order by a.id
/*
id pid name
---- ---- ----------
003 001 深圳市
005 003 罗湖区
006 003 福田区
007 003 宝安区
008 007 西乡镇
009 007 龙华镇
010 007 松岗镇(所影响的行数为 7 行)
*/
@id2 int
as
select count(*)
from tb
where id2=@id2
@id2 int
as
select count(*)
from tb
where id2=@id2
@id2 int
as
select count(*)
from tb
where id2=@id2
@id2 int
as
select count(*)
from tb
where id2=@id2
create table tb(id1 int, id2 int)
go
insert into tb values (1, 0)
insert into tb values (2, 1)
insert into tb values (3, 1)
insert into tb values (4, 2)
insert into tb values (5, 2)
insert into tb values (6, 2)
insert into tb values (7, 4)
insert into tb values (8, 4)
gowith t(id1, id2, lv) as
(
select id1, id2, 1 lv from tb where id2 = 0
union all
select tb.id1, tb.id2, t.lv + 1 lv from tb, t where tb.id2 = t.id1
)
select count(*) from t where lv = 3;
go
create table K1
(
id1 int,
id2 int,
content nvarchar(30)
)
insert into K1
select 1 , 0 , 'a' union all
select 2, 1 , 'ab' union all
select 3 , 1 , 'ac' union all
select 4 , 2 , 'abc' union all
select 5 , 2 , 'abd' union all
select 6 , 3 , 'acb' union all
select 7 , 3 , 'acd' union all
select 8 , 3 , 'acc' union all
select 9 , 4 , 'abcb'
create function re(@id as int)
returns @table_re table(id int,level int)
as
begin
declare @level int
set @level=1
insert into @table_re select @id,@level
while @@ROWCOUNT > 0
begin
set @level=@level+1
insert into @table_re select a.id1,@level
from K1 a,@table_re b
where a.id2=b.id and b.level=@level-1
end
return
end
go
----select a.* from tb a , f_cid('001') b where a.id = b.id order by a.id
select a.* from K1 a,re(3) b where a.id1=b.id order by a.id1
insert into BOM select 1,0,'aa'
insert into BOM select 2,1,'bb'
insert into BOM select 3,1,'ccc'
insert into BOM select 4,2,'ddd'
insert into BOM select 5,3,'eee'
insert into BOM select 6,5,'fff'
insert into BOM select 7,6,'ggg'create function dbo.ShowTree(@id int)
returns @Tree table (id int,msg varchar(100))
as
begin
declare @i int
set @i=1
insert into @Tree select @id,@i
while @@rowcount>0
begin
insert into @Tree select B.pid,@i from BOM B,@Tree T where T.ID=B.pid and T.msg=@i-1
end
return
end
select ID from dbo.ShowTree(2)
insert into tb values(1 , 0 , 'a')
insert into tb values(2 , 1 , 'ab')
insert into tb values(3 , 1 , 'ac')
insert into tb values(4 , 2 , 'abc')
insert into tb values(5 , 2 , 'abd')
insert into tb values(6 , 3 , 'acb')
insert into tb values(7 , 3 , 'acd')
insert into tb values(8 , 3 , 'acc')
insert into tb values(9 , 4 , 'abcb')
go--查询指定节点及其所有子节点的函数
create function f_cid(@ID1 int) returns @t_level table(id1 int , level int)
as
begin
declare @level int
set @level = 1
insert into @t_level select @id1 , @level
while @@ROWCOUNT > 0
begin
set @level = @level + 1
insert into @t_level select a.id1 , @level
from tb a , @t_Level b
where a.id2 = b.id1 and b.level = @level - 1
end
return
end
go--调用函数查询1及其所有子节点
select a.* from tb a , f_cid(1) b where a.id1 = b.id1 order by a.id1
/*
id1 id2 content
----------- ----------- ----------
1 0 a
2 1 ab
3 1 ac
4 2 abc
5 2 abd
6 3 acb
7 3 acd
8 3 acc
9 4 abcb(所影响的行数为 9 行)
*/--调用函数查询2及其所有子节点
select a.* from tb a , f_cid(2) b where a.id1 = b.id1 order by a.id1
/*
id1 id2 content
----------- ----------- ----------
2 1 ab
4 2 abc
5 2 abd
9 4 abcb(所影响的行数为 4 行)
*/drop table tb
drop function f_cid
create table tb(id1 int, id2 int, content varchar(10))insert into tb values(1 , 0 , 'a')
insert into tb values(2 , 1 , 'ab')
insert into tb values(3 , 1 , 'ac')
insert into tb values(4 , 2 , 'abc')
insert into tb values(5 , 2 , 'abd')
insert into tb values(6 , 3 , 'acb')
insert into tb values(7 , 3 , 'acd')
insert into tb values(8 , 3 , 'acc')
insert into tb values(9 , 4 , 'abcb') declare @a int
set @a=2 --指定节点
select * from tb
where content like (select
'%'+content+'%'
from
tb
where tb.id1=@a)
drop table tb
IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb
GO
CREATE TABLE tb(id1 INT IDENTITY(1,1),id2 INT,content VARCHAR(10))
GO
INSERT INTO tb
SELECT 0,'a' UNION ALL
SELECT 1,'ab' UNION ALL
SELECT 1,'ac' UNION ALL
SELECT 2,'abc' UNION ALL
SELECT 2,'abd' UNION ALL
SELECT 3,'acb' UNION ALL
SELECT 3,'acd' UNION ALL
SELECT 3,'acc' UNION ALL
SELECT 4,'abcb'
GOSELECT * FROM tbIF OBJECT_ID('uf_getSub') IS NOT NULL
DROP FUNCTION uf_getSub
GO
CREATE FUNCTION uf_getSub(@id1 int)
returns @t table(id1 int,level int)
as
begin
declare @level int
set @level=1
insert into @t select @id1,@level
while (@@rowcount>0)
begin
set @level=@level+1
insert into @t select id1,@level from tb where id2 in (select id1 from @t where level=@level-1)
end
return
end
goselect * from tb where id1 in (select id1 from uf_getSub(2))