Expresión de tabla común(CTE) con ejemplos practicos

Una expresión de tabla común utilizan el comando With de SQL , también llamada CTE en forma abreviada, es un conjunto de resultados con nombre temporal al que puede hacer referencia dentro de una instrucciones DML ademas vista. estas intrucciones funcionan en mysql , oracle o sql server en cualquiera de sus versiones todas.

Cuando hablamos de CTE hablamos de una tabla que mantiene en memoria el resultado de una consulta, que podremos llamar luego dentro de esa misma consulta. Esta cláusula también se puede utilizar en una instrucción CREATE VIEW como parte de la instrucción SELECT que la define.

En este artículo, veremos en detalle cómo crear y usar CTE desde nuestro servidor SQL.

Índice

¿Qué son expresión común de tabla?

Es un conjunto de instrucciones encapsuladas en un alias las cuales pueden contener las instrucciones mas comunes de los comandos DML lo cuales agrupan las siguientes clausulas de sql: SELECT, INSERT, UPDATE o DELETE. El CTE también se la puede usar en una vista.

Esta compuesto por miembros ancla, recursivos y consultas externas.

Sintaxis With sql como funciona

La sintaxis básica de una CTE sería la siguiente:

with CTE_nombre as ( Select columnas From tabla )

Para ver el resultado CTE favor ejecutar la siguiente consulta:

Select * From CTE_nombre

La consulta CTE comienza con un “WITH” y es seguida por el Nombre de expresión. Utilizaremos este nombre de expresión en nuestra consulta de selección para mostrar el resultado de nuestra consulta CTE y escribiremos nuestra definición de consulta CTE.

Para visualizar el resultado CTE usamos una consulta con un SELECT con el nombre de la expresión CTE.

Expresión de tabla común (Comando With)

Select * from nombredenuestra_CTE

Tipos de expresiónes CTE

Sintaxis CTE no recursivos

Los CTE no recursivos son aquellos que no ejecturan ningun ciclo de sql para repetir una accion varias veces. Crearemos un CTE simple solo debemos listar los nombre de los clientes de una base de datos de venta.

Según la sintaxis de CTE, cada consulta de CTE comenzará con un “WTTH” seguido del nombre de Expresión de CTE con la lista de columnas.

WTIH CTE_nombre_cliente as ( Select nombreCliente From Catalogo cliente)
Select * From CTE_nombre_cliente

El siguiente ejemplo de expresión de tabla común es muy sencillo, en el mismo estamos utilizando una tabla de clientes, seguido del comando select haciendo referencia al CTE en en el From

Sintaxis CTE recursivo

Los CTE recursivos son ciclos o bucles repetidos dependiendo de tu SGBD tendrias el uso del while o FOR de sql comando utilizados para hacer bucles de ejecusion. La recursividad hace referencia a todo procediemiento que se llama a sí mismo hasta que se cumpla una las condiciones.

Veremos cómo crear una Expresion CTE recursiva simple para contar y mostrar los primeros 10 registros usando un CTE y un UNION ALL.

Declare @Contador int =1;

With CONTADORCTE as

(

SELECT @Contador as Contador

UNION ALL

SELECT Contador +1

FROM CONTADORCTE

WHERE Contador < 10

)

SELECT * FROM CONTADORCTE

Aquí nosotros hemos estado usando solo una columna la cual se estara sumando uno hasta tanto los valores en la columna sumen 10 registros.

CTE para filtrar y formatear fechas

Consideremos que hay un escenario para mostrar el día desde la fecha de inicio hasta la fecha de finalización, una por una, como cada fila con detalles. Para mostrar los datos recursivos, utilizaremos la consulta CTE.

Aquí podemos ver que nosotros hemos creado una consulta CTE simple para mostrar el número de fila, la fecha de inicio y el número de semana. Cuando llega el momento ejecutamos esto, y obtendremos un solo resultado con RowNo como “1 “, StartDate como fecha actual y número de semana junto con el día de la semana.

Declare @fechainicio datetime,
        @Fechafin datetime;

select  @fechainicio= getdate(),
        @Fechafin = getdate()+10;
;with FechaCTE as
   (
      SELECT 1 as Registro,@fechainicio as Inicio,’W – ‘+convert(varchar(2),
            DATEPART( wk, @fechainicio))+’ / D (‘+convert(varchar(2),@fechainicio,106)+’)’ as Semana
    )
select Registro,Convert(varchar(10),fechainicio ,105)  as StartDate ,WeekNumber from FechaCTE

Salida: cuando ejecutamos la consulta, podemos ver la salida a continuación.

Ejemplo de crear una vista con CTE

Tambien es posible crear una vista usando CTE con estas expresion utilizaremos nuestro ejemplo anterior del CTE recursivo el cual te

Consulta de ejemplo:

CREATE VIEW CTEVIEWContador
AS

Declare @Contador int =1;
With CONTADORCTE as
   (
      SELECT @Contador as Contador
UNION ALL
      SELECT  Contador +1
  FROM  CONTADORCTE
  WHERE Contador < 10
    )
SELECT * FROM CONTADORCTE
GO

— T-SQL test view
SELECT * FROM CTEVIEWContador

Salida: cuando ejecutamos la consulta, podemos ver la salida a continuación como resultado de la vista. la cual es exactmente el mismo resultado de 1 al 10.

Conclusion

Este comando se puede ser utilizado en mysql , oracle , sql server , mariadb en cual quiera de sus versiones, el mismo es parte de los comando estandardizado de SQL. Los CTE son una gran herramienta para realizar todo tipo de operaciones y pueden ser de mucha ayuda para facilitar la lectura y soporte del codigo sql. ahora cuentanos como usas tu los CTE.

Hasta la proxima!!

Bye :D!!