我可以使用多个“with"吗? [英] Can I use multiple "with"?

问题描述

举个例子:

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".

另外，我想用 first with 里面的 second with.是真的还是我需要使用临时表?

Also, I want to use first with inside second with. Is it real or I need to use temp tables?

推荐答案

尝试:

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.

这篇关于我可以使用多个“with"吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！