with recursive postgres instruccion ejemplo cte consulta common sql-server union common-table-expression

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;

CON common_table_expression (Transact-SQL)