T-SQL with关键字 with as 递归循环表

WITH EmpsCTE AS
(
 SELECT EmployeeID, ReportsTo, FirstName, LastName
 FROM dbo.Employees
 WHERE EmployeeID = 5
 UNION ALL

 SELECT EMP.EmployeeID, EMP.ReportsTo, EMP.FirstName, EMP.LastName
 FROM EmpsCTE AS MGR
  JOIN dbo.Employees AS EMP
   ON EMP.ReportsTo = MGR.EmployeeID
)
SELECT * FROM EmpsCTE;

SET ANSI_NULLS ON  
GO  
SET QUOTED_IDENTIFIER ON  
GO  
-- =============================================  
-- Author:      KenshinCui  
-- Create date: 2011.12.30  
-- Description: 取得所有子节点  
-- =============================================  
CREATE FUNCTION GetChildren  
(  
    @ID INT  
)  
RETURNS   
@tbChildren TABLE   
(  
    ID INT   
)  
AS  
BEGIN  
      
    WITH ChildrenCTE(ID)  
    AS  
    (  
        SELECT ID FROM dbo.Tree WHERE parentID=@ID  
        UNION ALL  
        SELECT Tree.ID FROM Tree INNER JOIN ChildrenCTE ON ChildrenCTE.ID = Tree.parentID  
    )  
    INSERT INTO @tbChildren  SELECT Id FROM ChildrenCTE  
      
    RETURN  
END  
GO  

http://www.cnblogs.com/wwan/archive/2011/02/24/1964279.html

http://blog.csdn.net/kenshincui/article/details/7166379