SQL 我可以使用多个“with”吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5375634/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Can I use multiple "with"?
提问by cnd
Just for example:
举个例子：
With DependencedIncidents AS
(
SELECT INC.[RecTime],INC.[SQL] AS [str] FROM
(
SELECT A.[RecTime] As [RecTime],X.[SQL] As [SQL] FROM [EventView] AS A
CROSS JOIN [Incident] AS X
WHERE
patindex('%' + A.[Col] + '%', X.[SQL]) > 0
) AS INC
)
With lalala AS
(
SELECT INC.[RecTime],INC.[SQL] AS [str] FROM
(
SELECT A.[RecTime] As [RecTime],X.[SQL] As [SQL] FROM [EventView] AS A
CROSS JOIN [Incident] AS X
WHERE
patindex('%' + A.[Col] + '%', X.[SQL]) > 0
) AS INC
)
...doesn't work. "Error near With".
...不起作用。“与附近的错误”。
Also, I want to use first with inside second with. Is it real or I need to use temp tables?
另外，我想使用 first with inside second with。它是真的还是我需要使用临时表？
回答by Tomek Szpakowicz
Try:
尝试：
With DependencedIncidents AS
(
SELECT INC.[RecTime],INC.[SQL] AS [str] FROM
(
SELECT A.[RecTime] As [RecTime],X.[SQL] As [SQL] FROM [EventView] AS A
CROSS JOIN [Incident] AS X
WHERE
patindex('%' + A.[Col] + '%', X.[SQL]) > 0
) AS INC
),
lalala AS
(
SELECT INC.[RecTime],INC.[SQL] AS [str] FROM
(
SELECT A.[RecTime] As [RecTime],X.[SQL] As [SQL] FROM [EventView] AS A
CROSS JOIN [Incident] AS X
WHERE
patindex('%' + A.[Col] + '%', X.[SQL]) > 0
) AS INC
)
And yes, you can reference common table expression inside common table expression definition. Even recursively. Which leads to some very neat tricks.
是的，您可以在公用表表达式定义中引用公用表表达式。甚至递归。这导致了一些非常巧妙的技巧。
回答by marc_s
Yes - just do it this way:
是的 - 就这样做：
WITH DependencedIncidents AS
(
....
),
lalala AS
(
....
)
You don't need to repeat the WITHkeyword
您不需要重复WITH关键字
