여러 CTE와 연합하려면 어떻게 해야 합니까?

여러 CTE와 연합하려면 어떻게 해야 합니까?

사용 방법UNION배수의Common Table Expressions?

요약 번호를 몇 개 작성하려고 하는데 어디에 두든 간에;항상 오류가 발생합니다.

SELECT  COUNT(*)
FROM    dbo.Decision_Data
UNION
SELECT  COUNT(DISTINCT Client_No)
FROM    dbo.Decision_Data
UNION
WITH    [Clients]
          AS ( SELECT   Client_No
               FROM     dbo.Decision_Data
               GROUP BY Client_No
               HAVING   COUNT(*) = 1
             )
    SELECT  COUNT(*) AS [Clients Single Record CTE]
    FROM    Clients;

위의 예에서는 단일 CTE를 처음으로 이동할 수 있지만 여러 CTE가 있습니다.UNION

여러 CTE를 결합하려는 경우 먼저 CTE를 선언한 다음 사용해야 합니다.

With Clients As
    (
    Select Client_No
    From dbo.Decision_Data
    Group By Client_No
    Having Count(*) = 1
    )
    , CTE2 As
    (
    Select Client_No
    From dbo.Decision_Data
    Group By Client_No
    Having Count(*) = 2
    )
Select Count(*)
From Decision_Data
Union
Select Count(Distinct Client_No)
From dbo.Decision_Data
Union
Select Count(*)
From Clients
Union
Select Count(*)
From CTE2;

한 CTE를 다른 CTE에서 사용할 수도 있습니다.

With Clients As
        (
        Select Client_No
        From dbo.Decision_Data
        Group By Client_No
        Having Count(*) = 1
        )
        , CTE2FromClients As
        (
        Select Client_No
        From Clients
        )
    Select Count(*)
    From Decision_Data
    Union
    Select Count(Distinct Client_No)
    From dbo.Decision_Data
    Union
    Select Count(*)
    From Clients
    Union
    Select Count(*)
    From CTE2FromClients;

with common_table_expression(Transact-SQL)

다음과 같이 할 수 있습니다.

WITH    [Clients]
          AS ( SELECT   Client_No
               FROM     dbo.Decision_Data
               GROUP BY Client_No
               HAVING   COUNT(*) = 1
             ),
        [Clients2]
          AS ( SELECT   Client_No
               FROM     dbo.Decision_Data
               GROUP BY Client_No
               HAVING   COUNT(*) = 1
             )
SELECT  COUNT(*)
FROM    Clients
UNION
SELECT  COUNT(*)
FROM    Clients2;

언급URL : https://stackoverflow.com/questions/11542288/how-do-you-union-with-multiple-ctes