遇到一个需求，需要在sqlserver中实现递归查询，也就是实现oracle中的CONNECT BY PRIOR 功能。在stackoverflow查到了解决方案，记录下。

The SQL standard way to implement recursive queries, as implemented e.g. by IBM DB2 and SQL Server, is the WITH clause. See this article for one example of translating a CONNECT BY into a WITH (technically a recursive CTE) — the example is for DB2 but I believe it will work on SQL Server as well.

Edit: apparently the original querant requires a specific example, here’s one from the IBM site whose URL I already gave. Given a table:

CREATE TABLE emp(empid  INTEGER NOT NULL PRIMARY KEY, 
                 name   VARCHAR(10), 
                 salary DECIMAL(9, 2), 
                 mgrid  INTEGER); 

where mgrid references an employee’s manager’s empid, the task is, get the names of everybody who reports directly or indirectly to Joan. In Oracle, that’s a simple CONNECT:

SELECT name  
  FROM emp 
  START WITH name = 'Joan' 
  CONNECT BY PRIOR empid = mgrid 

In SQL Server, IBM DB2, or PostgreSQL 8.4 (as well as in the SQL standard, for what that’s worth;-), the perfectly equivalent solution is instead a recursive query (more complex syntax, but, actually, even more power and flexibility):

WITH n(empid, name) AS  
   (SELECT empid, name  
    FROM emp 
    WHERE name = 'Joan' 
        UNION ALL 
    SELECT nplus1.empid, nplus1.name  
    FROM emp as nplus1, n 
    WHERE n.empid = nplus1.mgrid) 
SELECT name FROM n 

Oracle’s START WITH clause becomes the first nested SELECT, the base case of the recursion, to be UNIONed with the recursive part which is just another SELECT.

SQL Server’s specific flavor of WITH is of course documented on MSDN, which also gives guidelines and limitations for using this keyword, as well as several examples.

原文地址：http://stackoverflow.com/questions/959804/simulation-of-connect-by-prior-of-oracle-in-sql-server

另附中文版的一个：

http://www.cnblogs.com/xbf321/archive/2009/04/30/1446833.html