CTE 递归查询

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Employee_Tree]') AND type in (N'U'))
DROP TABLE [dbo].[Employee_Tree]


CREATE TABLE [dbo].[Employee_Tree](
[Employee_NM] [nvarchar](50) NULL,
[Employee_ID] [int] NOT NULL,
[ReportsTo] [int] NULL)


INSERT INTO Employee_Tree VALUES('Richard', 1, NULL)
INSERT INTO Employee_Tree VALUES('Stephen', 2, 1)
INSERT INTO Employee_Tree VALUES('Clemens', 3, 2)
INSERT INTO Employee_Tree VALUES('Malek', 4, 2)
INSERT INTO Employee_Tree VALUES('Goksin', 5, 4)
INSERT INTO Employee_Tree VALUES('Kimberly', 6, 1)
INSERT INTO Employee_Tree VALUES('Ramesh', 7, 5)


;WITH    SimpleRecursive ( Employee_NM, Employee_ID, ReportsTo )
          AS ( SELECT   Employee_NM ,
                        Employee_ID ,
                        ReportsTo
               FROM     Employee_Tree
               WHERE    Employee_ID = 4
               UNION ALL
               SELECT   p.Employee_NM ,
                        p.Employee_ID ,
                        p.ReportsTo
               FROM     Employee_Tree p
                        INNER JOIN SimpleRecursive A ON A.ReportsTo = p.Employee_ID
             )             
SELECT * FROM SimpleRecursive