[sql] 여러 개의 “with”를 사용할 수 있습니까?

Program / 글쓴이

예를 들면 다음과 같습니다.

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
)

… 작동하지 않습니다. “With near near”입니다.

또한 내부 초를 먼저 사용하고 싶습니다. 실제입니까 아니면 임시 테이블을 사용해야합니까?

답변

시험:

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
)

그리고 공통 테이블 표현식 정의 내에서 공통 테이블 표현식을 참조 할 수 있습니다. 재귀 적으로도. 이것은 매우 깔끔한 트릭으로 이어집니다 .

답변

예-그냥 이렇게하십시오 :

WITH DependencedIncidents AS
(
  ....
),
lalala AS
(
  ....
)

WITH키워드 를 반복 할 필요가 없습니다

답변