您如何与多个CTE一起使用UNION？ [英] How do you UNION with multiple CTEs?

问题描述

如何将 UNION 与多个公用表表达式一起使用？

How do you use UNION with multiple Common Table Expressions?

我正在尝试汇总一些汇总数字，但是无论我将放在哪里； ，我总是会出错

I'm trying to put together some summary numbers but no matter where I put the ;, I always get an error

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，但我想加入 UNION

UPDATE: I appreciate in the example above I can move the single CTE to the beginning, but I have a number of CTEs I'd like to UNION

推荐答案

如果要合并多个CTE，则需要先声明CTE，然后再使用它们：

If you are trying to union multiple CTEs, then you need to declare the CTEs first and then use them:

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：

You can even use one CTE from another:

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;

使用common_table_expression（Transact-SQL）

这篇关于您如何与多个CTE一起使用UNION？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！