SELECT FolderID, FolderName, FolderFid, NameModify, NameView, UserID FROM dbo.fun_folder_left(8) 记录如下
----------------------
72 1111111111111111 72 False False 8
75 111111111111.1 72 NULL NULL NULL
76 2222222222222222.1 73 False False 8
77 22222222222222222.2 73 NULL NULL NULL
78 33333333333333333333.1 74 NULL NULL NULL
80 2222222222222222.1.2 76 NULL NULL NULL
如果是 如下的sql
SELECT FolderID, FolderName, FolderFid, NameModify, NameView, UserID
FROM dbo.fun_folder_left(8) AS fun_folder_left_1
WHERE (FolderFid IN (SELECT FolderID FROM dbo.fun_folder_left(8) AS fun_folder_left_2))
记录如下
----------------------72 1111111111111111 72 False False 8
75 111111111111.1 72 NULL NULL NULL
80 2222222222222222.1.2 76 NULL NULL NULL----------------------------------------可是我想要的结果是72 1111111111111111 72 False False 8
75 111111111111.1 72 NULL NULL NULL怎么用一个函数 让他自己去循环啊! 一直执行到 第三步这样效果谢谢
----------------------
72 1111111111111111 72 False False 8
75 111111111111.1 72 NULL NULL NULL
76 2222222222222222.1 73 False False 8
77 22222222222222222.2 73 NULL NULL NULL
78 33333333333333333333.1 74 NULL NULL NULL
80 2222222222222222.1.2 76 NULL NULL NULL
如果是 如下的sql
SELECT FolderID, FolderName, FolderFid, NameModify, NameView, UserID
FROM dbo.fun_folder_left(8) AS fun_folder_left_1
WHERE (FolderFid IN (SELECT FolderID FROM dbo.fun_folder_left(8) AS fun_folder_left_2))
记录如下
----------------------72 1111111111111111 72 False False 8
75 111111111111.1 72 NULL NULL NULL
80 2222222222222222.1.2 76 NULL NULL NULL----------------------------------------可是我想要的结果是72 1111111111111111 72 False False 8
75 111111111111.1 72 NULL NULL NULL怎么用一个函数 让他自己去循环啊! 一直执行到 第三步这样效果谢谢
into #t
union all
select 75,72
union all
select 76,73
union all
select 77,73
union all
select 78,74
union all
select 80,76declare @rowcnt int
set @rowcnt = 1
while(@rowcnt<>0)
begin
delete from #t
where col2 not in
(select col1 from #t)
select @rowcnt = @@rowcount
endselect * from #tdrop table #t结果
-----------
72 72
75 72
begin
从临时表中删掉不满足条件的记录
删除的行数保存到变量中
end@@rowcount 是delete操作影响的行数
set QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[fun_get_leftlist]
(
@userid int
)
RETURNS @ret table(FolderID int,FolderName nvarchar(50),FolderFid int,NameView bit,UserID int )
ASBEGIN
declare @rowcnt int
set @rowcnt = 1
insert into @ret
SELECT FolderID,FolderName, FolderFid,NameView,UserID
FROM dbo.fun_folder_left(@userid)
while(@rowcnt<>0)
begin
delete from @ret
where FolderFid not in
(select FolderID from @ret )
select @rowcnt = @@rowcount
end