sql-server - postgres - sql server cte recursive

¿Cómo UNION con múltiples CTEs?

Question

¿Cómo usas UNION con múltiples Common Table Expressions ?

Estoy tratando de juntar algunos números de resumen, pero no importa dónde coloque el ; , Siempre me sale un 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;

ACTUALIZACIÓN: Aprecio que en el ejemplo anterior puedo mover el CTE único al principio, pero tengo un número de CTE que me gustaría unir a UNION

Answer 1

Puedes hacerlo así:

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;

Answer 2

Si está intentando unir varios CTE, debe declarar primero los CTE y luego usarlos:

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;

Incluso puedes usar un CTE de otro:

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;

CON common_table_expression (Transact-SQL)