tsql – t-sql WITH WITH WITH

发布时间：2020-12-12 08:50:26 所属栏目：MsSql教程来源：网络整理

导读：我必须对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

我必须对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 ...

http://www.4guysfromrolla.com/webtech/071906-1.shtml

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!