sql 2種遞迴

標籤：io   ar   cti   on   sp   sql   c   ui   

alter 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 @[email protected]+1
  INSERT @t_Level SELECT a.[MenuId],@Level
  FROM [MMS_Menu] a,@t_Level b
  WHERE a.[MenuParentId]=b.ID
   AND [email protected]
 END
 RETURN
END
GO
select * from f_Cid(101) b,[MMS_Menu] a where a.MenuId=b.ID

或

  with cte as

 (

 select a.ID,a.date,a.name,a.pID from dbo.testb a where [email protected]

 union all  

 select k.ID,k.date,k.name,k.pID  from dbo.testb k inner join cte c on c.ID = k.pID

 )
 select * from cte ;