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