您如何与多个CTE一起使用UNION？

本文介绍了您如何与多个CTE一起使用UNION？的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

如何将 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;

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

Cte