求一个递归的写法

[vfork]

pre_id, id (pre_id为父ID)

先输入ID, 我想把所有包括它的父ID都找出来.

[昵称被占用了]

一次找出所有父ID就不要用递归

写个函数

create function fn_AllPre_ID (
@Id int
)
returns @T table(
@id int
)
as
begin
insert @t select pre_id from tablename where id=@id
while exists (select 1 from tablename where id in (select id from @t)
and pre_id is not null )
insert @t select pre_id from tablename where id in (select id from @t)
and pre_id is not null
return
end


--调用
select * from db.fn_AllPre_ID(10)

[paoluo]

--建立測試環境
Create Table Tree
(pre_id Int,
id Int)
--插入數據
Insert Tree Select 1, 2
Union All Select 2, 3
Union All Select 3, 4
Union All Select 6, 7
Union All Select 7, 8
Union All Select 8, 9
Go
--建立函數
--得到所有的子節點
Create Function GetChild(@id Int)
Returns @Child Table(pre_id Int,id Int)
As
Begin
Insert @Child Select * From Tree Where pre_id=@id
While @@ROWCOUNT>0
Insert @Child Select B.* From @Child A Inner Join Tree B On A.id=B.pre_id Where B.pre_id Not In (Select Distinct pre_id From @Child)
Return
End
GO
--得到所有的父節點
Create Function GetParent(@id Int)
Returns @Parent Table(pre_id Int,id Int)
As
Begin
Insert @Parent Select * From Tree Where id=@id
While @@ROWCOUNT>0
Insert @Parent Select B.* From @Parent A Inner Join Tree B On A.pre_id=B.id Where B.id Not In (Select Distinct id From @Parent)
Return
End
GO
--測試
Select id From dbo.GetChild(2)
Select pre_id From dbo.GetParent(3)
GO
--刪除測試環境
Drop Table Tree
Drop Function GetChild,GetParent
Go
--結果
/*
id
3
4

pre_id
2
1
*/

[zjcxc]

-- 2005

declare @id int
set @id = 1
;with tree
as(
select * from tb where id = @id
union all
select a.* from tb a, tree b
where a.pre_id = b.id
)
select * from tree

[zlp321002]

--2005
DECLARE @ID INT
SET @ID=3;
WITH CTE_TREE
AS
(
SELECT * FROM TREE
WHERE ID=@ID
UNION ALL
SELECT A.* FROM TREE A,CTE_TREE B
WHERE A.ID=B.pre_id
)
SELECT pre_id FROM CTE_TREE