¿Cómo se UNE con múltiples CTE?
¿Cómo se usa UNION
con múltiples Common Table Expressions
?
Estoy tratando de reunir algunos números de resumen, pero no importa dónde ponga el ;
, siempre recibo 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 en el ejemplo anterior que puedo mover el simple CTE al principio, pero tengo un número de CTEs que me gustaría UNION
39
Author: brett rogers, 2012-07-18
2 answers
Si está tratando de unir varios CTEs, primero debe declarar los CTEs 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;
70
Author: Thomas,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2016-04-10 08:26:12
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2016-04-10 08:26:12
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;
11
Author: DaveShaw,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2012-07-18 13:29:30
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2012-07-18 13:29:30