[解決済み】"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
)

...うまくいきません。 "Error near With"です。

また、2番目のwithの中で1番目の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
),
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
)

そして、そうです、共通テーブル式定義の内部で共通テーブル式を参照することができます。再帰的にさえも。その結果、いくつかの非常に 巧妙なトリック .