SELECT FolderFid,FolderName, FolderID FROM Folder 0 111111111 1
0 222222222 2
1 333333333 3
2 4444444444 4
0 5555555555 5
1 6666666666 6SELECT userid,cango, FolderID FROM Folderpower 8 1 1
我就要一个函数 能通过folderpower的cango字段 判断 如果是1 的话我想遍历 FolderID 是1 的树我就输出 一个表如下
FolderFid,FolderName, FolderID ,userid ,
0 111111111 1 8
1 333333333 3 8
2 4444444444 4 8
1 6666666666 6 8
0 222222222 2
1 333333333 3
2 4444444444 4
0 5555555555 5
1 6666666666 6SELECT userid,cango, FolderID FROM Folderpower 8 1 1
我就要一个函数 能通过folderpower的cango字段 判断 如果是1 的话我想遍历 FolderID 是1 的树我就输出 一个表如下
FolderFid,FolderName, FolderID ,userid ,
0 111111111 1 8
1 333333333 3 8
2 4444444444 4 8
1 6666666666 6 8
INSERT tb SELECT 1,0,N'部门1'
UNION ALL SELECT 2,0,N'部门2'
UNION ALL SELECT 3,1,N'部门1.1'
UNION ALL SELECT 4,3,N'部门1.1.1'
UNION ALL SELECT 5,4,N'部门1.1.1.1'
UNION ALL SELECT 6,2,N'部门2.1'
UNION ALL SELECT 7,6,N'部门2.1.1'
UNION ALL SELECT 8,7,N'部门2.1.1.1'
GO--查询指定节点及其所有子节点的函数
CREATE FUNCTION f_Cid(@ID INT)
RETURNS @t_Level TABLE(ID INT,Level int)
AS
BEGIN
DECLARE @Level int
SET @Level=1
INSERT @t_Level SELECT @ID,@Level
WHILE @@ROWCOUNT>0
BEGIN
SET @Level=@Level+1
INSERT @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--调用函数查询节点[ 1 ]及其所有子节点
SELECT a.*
FROM tb a,f_Cid(1) b
WHERE a.ID=b.ID
/*--结果
ID PID Name
----------- ----------- ----------
1 0 部门1
3 1 部门1.1
4 3 部门1.1.1
5 4 部门1.1.1.1(4 row(s) affected)
--*/
DROP FUNCTION F_Cid
DROP TABLE TB
我的问题不再那啊,你就假设 你的fun_XX 实现了遍历了
我要的那个咋弄 谢谢
as
(
select folderfid,FolderName,folderid from tb where folderid=@id
union all
select A.folderfid,A.FolderName,A.folderid from cet_ A,tb B where A.folderid=B.folderfid
)
select * from Cet_
--> (让你望见影子的墙)生成测试数据,时间:2009-02-23
if not object_id('tb') is null
drop table tb
Go
Create table tb([FolderFid] int,[FolderName] varchar(20),[FolderID] int)
Insert tb
select 0,111111111,1 union all
select 0,222222222,2 union all
select 1,333333333,3 union all
select 2,4444444444,4 union all
select 0,5555555555,5 union all
select 1,6666666666,6
Go
Select * from tb--> (让你望见影子的墙)生成测试数据,时间:2009-02-23
if not object_id('tb2') is null
drop table tb2
Go
Create table tb2([userid] int,[cango] int,[FolderID] int)
Insert tb2
select 8,1,1
Go
Select * from tb2select k.*,(select userid from tb2 where folderid=1)
from
(select b.* from tb a,tb b where a.folderid=b.folderfid and a.folderid=1
union
select * from tb where folderid=1 )K 0 111111111 1 8
1 333333333 3 8
1 6666666666 6 8
insert into Folder values(0 , '111111111' , 1 )
insert into Folder values(0 , '222222222' , 2 )
insert into Folder values(1 , '333333333' , 3 )
insert into Folder values(2 , '4444444444', 4 )
insert into Folder values(0 , '5555555555', 5 )
insert into Folder values(1 , '6666666666', 6 )
create table Folderpower(userid int,cango int, FolderID int)
insert into Folderpower values(8,1,1)
go
--查询指定节点及其所有子节点的函数
create function f_cid(@FolderID int) returns @t_level table(FolderID int , level int)
as
begin
declare @level int
set @level = 1
insert into @t_level select @FolderID , @level
while @@ROWCOUNT > 0
begin
set @level = @level + 1
insert into @t_level select a.FolderID , @level
from Folder a , @t_Level b
where a.FolderFid = b.FolderID and b.level = @level - 1
end
return
end
go--调用函数查询FolderID = 1及其所有子节点
declare @cango as int
set @cango = (select top 1 cango from Folderpower)
select a.* from Folder a , f_cid(@cango) b where a.FolderID = b.FolderID order by a.FolderIDdrop table Folder , Folderpower
drop function f_cid/*FolderFid FolderName FolderID
----------- -------------------- -----------
0 111111111 1
1 333333333 3
1 6666666666 6(所影响的行数为 3 行)
*/
create function f_cid(@FolderID int) returns @t_level table(FolderID int , level int)
as
begin
declare @level int
set @level = 1
insert into @t_level select @FolderID , @level
while @@ROWCOUNT > 0
begin
set @level = @level + 1
insert into @t_level select a.FolderID , @level
from Folder a , @t_Level b
where a.FolderFid = b.FolderID and b.level = @level - 1
end
return
end
godeclare @cango as int
set @cango = 1
select a.*,c.userid from Folder a , f_cid(@cango) b ,folderpower c where a.FolderID = b.FolderID and c.cango=1 order by a.FolderID