none
怎样使用公用表表达式(CTE)实现递归查询? RRS feed

答案

  • 大家好,

    公用表表达式 (CTE)是在SQL Server 2005引入的。公用表表达式有个特性就是可以引用自身,因此我们可以通过使用它来实现递归查询。

    下面我们通过两个例子来演示一下:

    1) 首先创建范例表以范例数据

    CREATE TABLE #Employees (EmployeeID INT,FirstName VARCHAR(10),ManagerID INT)
    
    INSERT INTO #Employees VALUES (1,'John',NULL)
    INSERT INTO #Employees VALUES (2,'Tom',1)
    INSERT INTO #Employees VALUES (3,'Mark',1)
    INSERT INTO #Employees VALUES (4,'Steven',2)
    INSERT INTO #Employees VALUES (5,'Jim',4)
    INSERT INTO #Employees VALUES (6,'Nancy',3)
    
    
    正如所见,这是一张员工表,包含3列:员工编号,员工名字和上级编号。

    2) 如何按照级别列出整个公司的人员组织结构?
     
     WITH cte
    AS
    (
    	SELECT EmployeeID, FirstName, ManagerID, 0 AS 'Level'
    	FROM #Employees WHERE ManagerID IS NULL
    	UNION ALL
    	SELECT e.EmployeeID, e.FirstName, e.ManagerID, Level+1
    	FROM #Employees e
    	INNER JOIN cte c ON e.ManagerID = c.EmployeeID
    )
    
    SELECT EmployeeID, FirstName, ManagerID, Level
    FROM cte
    


    返回的结果集是:

    EmployeeID  FirstName  ManagerID   Level
    ----------- ---------- ----------- -----------
    1           John       NULL        0
    2           Tom        1           1
    3           Mark       1           1
    6           Nancy      3           2
    4           Steven     2           2
    5           Jim        4           3

    (6 row(s) affected)

    3) 如何找出雇员Jim(员工编号6)的所有上级?

    WITH cte
    AS
    (
    	SELECT EmployeeID, FirstName, ManagerID
    	FROM #Employees WHERE EmployeeID = 6
    	UNION ALL
    	SELECT e.EmployeeID, e.FirstName, e.ManagerID
    	FROM #Employees e
    	INNER JOIN cte c ON e.EmployeeID = c.ManagerID
    )
    
    SELECT EmployeeID, FirstName, ManagerID 
    FROM cte
    


    返回的结果集是:

    EmployeeID  FirstName  ManagerID
    ----------- ---------- -----------
    6           Nancy      3
    3           Mark       1
    1           John       NULL

    (3 row(s) affected)

    相关文档:

    使用公用表表达式的递归查询
    http://msdn.microsoft.com/zh-cn/library/ms186243.aspx

     

    谢谢。




    2011年4月19日 6:20
    版主