我必须对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在第二个WITH,我该怎么做？
解决方法
您可以在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 ...
