表fileinfo
FileId parentid
----------- -----------
1 0
3 0
4 0
5 4
19 5
20 5
21 19
31 20
32 20
35 31
36 32
38 20
39 5根据FileId查询所有的子节点
如where fileid=5
1.查出:
5 4
19 5
20 5
39 5
2.查出:
19 5
20 5
39 5
FileId parentid
----------- -----------
1 0
3 0
4 0
5 4
19 5
20 5
21 19
31 20
32 20
35 31
36 32
38 20
39 5根据FileId查询所有的子节点
如where fileid=5
1.查出:
5 4
19 5
20 5
39 5
2.查出:
19 5
20 5
39 5
FileId parentid
----------- -----------
5 4
19 5
20 5
21 19
31 20
32 20
35 31
36 32
38 20
39 5 查出这样的效果,这是根据fileid=20
FileId parentid
----------- -----------
20 5
31 20
32 20
35 31
36 32
38 20
表结构如下:
ptype subptype amount
a a.1 20
a a.2 15
a a.3 10
a.1 a.1.1 20
a.1 a.1.2 15
a.1 a.1.3 30
a.2 a.2.1 10
a.2 a.2.2 20
a.1.1 a.1.1.1 45
a.1.1 a.1.1.2 15
a.2.1 a.2.1.1 20
a.2.2 a.2.2.1 13函数如下:
CREATE FUNCTION fn_aaa (@ProductID varchar(5))
RETURNS @retPLExpand TABLE (Ptype varchar(50),SubPtype varchar(50),
Amount smallint NOT NULL
)
-- title nvarchar(30))
/*Returns a result set that lists all the employees who report to given
employee directly or indirectly.*/
AS
BEGIN
DECLARE @RowsAdded int
declare @PLExpand Table (Ptype varchar(50),SubPtype varchar(50),
Amount smallint NOT NULL,
processed tinyint default 0) INSERT @PLExpand
SELECT Ptype,SubPtype, Amount, 0
FROM aaa
WHERE Ptype = @ProductID
SET @RowsAdded = @@rowcount
-- While new employees were added in the previous iteration WHILE @RowsAdded > 0
BEGIN
/*Mark all employee records whose direct reports are going to be
found in this iteration with processed=1.*/
UPDATE @PLExpand
SET processed = 1
WHERE processed = 0
-- Insert employees who report to employees ed 1.
INSERT @PLExpand
SELECT Ptype, SubPtype, Amount, 0
FROM aaa
WHERE ltrim(Ptype) in (select ltrim(subptype) from @PLExpand where processed = 1)
SET @RowsAdded = @@rowcount
/*Mark all employee records whose direct reports have been found
in this iteration.*/
UPDATE @PLExpand
SET processed = 2
WHERE processed = 1
END
-- copy to the result of the function the required columns
INSERT @retPLExpand
SELECT Ptype, SubPtype, Amount
FROM @PLExpand
RETURN
END调用方法如下:
select * from fn_aaa('a.1')
意思是找出a.1下的所有儿子及孙子.
go
create table tb(code int,su_code int)
insert into tb(code,su_code) values(1,null)
insert into tb(code,su_code) values(2,1)
insert into tb(code,su_code) values(3,1)
insert into tb(code,su_code) values(4,3)
insert into tb(code,su_code) values(5,2)
insert into tb(code,su_code) values(6,3)
insert into tb(code,su_code) values(7,3)
insert into tb(code,su_code) values(8,4)
go
create table #stack(item int,levels int)
delete from #stack
set nocount on
go
declare @top int
declare @level int
declare @line varchar(128)
select @top=code from tb where su_code is null
insert into #stack values(@top,1)
select @level=1
while @level>0
begin
if exists(select * from #stack where levels=@level)
begin
select @top=item from #stack where levels=@level
select @line=space(@level-1)+convert(varchar,@top)
print @line
delete from #stack where levels=@level and item=@top
insert into #stack select code,@level+1 from tb where su_code=@top
if @@rowcount>0
select @level=@level+1
end
else
select @level=@level-1
end
抄别人的,就是用一个递归查询,还有好多种方法你找下吧
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 行)
*/ drop table tb
drop function f_cid
----------------------------------------------------------------------------------------------------------------
create table 表(levelid int,levelname char(2),parent int)
insert 表 select 1, 'AA' , 0
union all select 2 , 'BB' , 1
union all select 3 , 'CC' , 1
union all select 4 , 'DD' , 2
union all select 5 , 'EE' , 3
union all select 6 , 'FF', 5create function bom (@name char(2))
returns @tb table (levelid int,levelname char(2),parent int)
as
begin
insert @tb select levelID,LevelName,parent from 表 where Levelname = @name
while @@rowcount > 0
insert @tb select levelID,LevelName,parent from 表
where parent in (select levelID from @tb)
and levelID not in (select levelID from @tb)
return
endselect * from dbo.bom('bb')
levelid levelname parent
----------- --------- -----------
2 BB 1
4 DD 2(所影响的行数为 2 行)
我晕,那我要把父子节点都查出 来怎么找,最后都放到List上呢
如where fileid=5
1.查出:
5 4
19 5
20 5
39 5
2.查出:
19 5
20 5
39 5
这个一句话可以
--------------------
查出这样的效果,这是根据fileid=5
FileId parentid
----------- -----------
5 4
19 5
20 5
21 19
31 20
32 20
35 31
36 32
38 20
39 5查出这样的效果,这是根据fileid=20
FileId parentid
----------- -----------
20 5
31 20
32 20
35 31
36 32
38 20
这个参考LS诸位大大的
select 1, 0 union all
select 3, 0 union all
select 4, 0 union all
select 5, 4 union all
select 19, 5 union all
select 20, 5 union all
select 21, 19 union all
select 31, 20 union all
select 32, 20 union all
select 35, 31 union all
select 36, 32 union all
select 38, 20 union all
select 39, 5 --select * from @tb
;with mycte(FieldID,ParentID)
as
(
select FieldID,ParentID from @tb where FieldID=5
union all
select a.FieldID,a.ParentID from @tb a inner join mycte b on a.ParentID=b.FieldID
)select * from mycte
查出这样的效果,这是根据fileid=5
FileId parentid
----------- -----------
5 4
19 5
20 5
21 19
31 20
32 20
35 31
36 32
38 20
39 5 查出这样的效果,这是根据fileid=20
FileId parentid
----------- -----------
20 5
31 20
32 20
35 31
36 32
38 20 这个效果才是对的,
with mycte(FieldID,ParentID)
这个不清楚什么意思根/*--WHILE语句-*/语句不一样吧