Saltar al contenido

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


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!!