T-SQL中的WITH子句用法详解

Question

我需要在 WITH 查询中进行查询，类似于：

; WITH #table1  
(  
SELECT id, x from ... WHERE....  
UNION ALL  
 SELECT id, x from ... WHERE...  
)  

WITH #table2  
(  
 SELECT DISTINCT tbl_x.*,ROW_NUMBER() OVER (order by id) as RowNumber  
WHERE id in ( SELECT id from #table1)  
)  

SELECT * FROM #table2 WHERE RowNumber > ... and ...

我必须在第一个WITH上使用WITH，然后在第二个WITH上使用SELECT，我该怎么做？

- Mertez

Answer 1

在 WITH 关键字后，通过用逗号分隔每个 CTE，您可以定义多个 CTE。

WITH T1 AS
(
    SELECT id, x from ... WHERE....
    UNION ALL
    SELECT id, x from ... WHERE...
)
, T2 AS
(
    SELECT DISTINCT tbl_x.*, ROW_NUMBER() OVER (order by id) as RowNumber
    WHERE id in ( SELECT id from T1 )
)
SELECT * FROM T2 WHERE RowNumber > ... and ...

https://web.archive.org/web/20210927200924/http://www.4guysfromrolla.com/webtech/071906-1.shtml

- Tim Schmelter

---

WITH 关键字只需使用一次。 - HABO