sql-server - postgres - sql server cte recursive
¿Cómo UNION con múltiples CTEs? (2)
¿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
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;
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;