Breaking

Post Top Ad

Your Ad Spot

domingo, 15 de diciembre de 2019

Expresiones comunes de tabla de SQL Server (CTE)

¿Qué es una expresión común de tabla?

Una expresión de tabla común, también llamada CTE en forma abreviada, es un conjunto de resultados con nombre temporal al que puede hacer referencia dentro de una instrucción SELECT, INSERT, UPDATE o DELETE. El CTE también se la puede usar en una vista.
En este artículo, veremos en detalle cómo crear y usar CTE desde nuestro servidor SQL.

Sintaxis y ejemplos de expresiones comunes de tabla

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 ver el resultado CTE usamos una consulta con un SELECT con el nombre de la expresión CTE.
O

Tipos de expresión comunes de tabla (CTE)

Hay dos tipos de CTE: recursivos y no recursivos
CTE no recursivos
Los CTE no recursivos son simples cuando el CTE no utiliza ninguna recursividad o procesamiento repetido de una subrutina. Crearemos un CTE simple no recursivo para mostrar el número de fila del 1 al 10.
Según la sintaxis de CTE, cada consulta de CTE comenzará con un “Con” seguido del nombre de Expresión de CTE con la lista de columnas.
Aquí nosotros hemos estado usando solo una columna como ROWNO. La siguiente es la parte de Consulta, aquí nosotros escribimos nuestra consulta de selección para que se ejecute para nuestro CTE. Después de crear nuestra consulta CTE para ejecutar el CTE, use la instrucción select con el nombre de Expresión CTE.
Salida: cuando ejecutamos la consulta, podemos ver la salida a continuación.
CTE recursivo
Los CTE recursivos son ciclos o bucles de procedimiento repetidos, también conocidos como recursividad. La consulta recursiva se llama a sí misma hasta que la consulta satisfaga la condición. En un CTE recursivo, nosotros debemos proporcionar una condición where para terminar la recursión.
Veremos cómo crear una consulta recursiva simple para mostrar el número de fila del 1 al 10 usando un CTE.
En primer lugar, declaramos la variable Integer como ” RowNo ” y establecemos el valor predeterminado en 1 y entonces hemos creado nuestra primera consulta CTE como un nombre de expresión, “ROWCTE”. En nuestro CTE, primero mostraremos por defecto el número de fila predeterminado y luego usaremos Union ALL para incrementar y mostrar el número de fila 1 en uno hasta que el número de fila alcance el valor incrementado a 10. Para ver el resultado, usaremos una consulta de selección para mostrar nuestro resultado CTE.
Salida: cuando ejecutamos la consulta, podemos ver la salida a continuación.
Consulta CTE para mostrar el intervalo de 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í escribiremos una consulta CTE para mostrar el rango de fechas con el número de semana y el día. Para esto nosotros establecemos la fecha de inicio y finalización en el parámetro. Aquí, en este ejemplo, hemos utilizado getdate( ) para establecer la fecha de inicio como la fecha de hoy, y para la fecha de finalización agregamos 16 días a partir de hoy.
CTE sin Union All
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.
Salida: cuando ejecutamos la consulta, podemos ver la salida a continuación.
CTE con Union All
Para mostrar el resultado desde la fecha de inicio hasta la fecha de finalización uno por uno como recursivo, nosotros usamos Union All para poder incrementar el RowNo, y para agregar el día uno por uno hasta que la condición nos satisfaga el rango de fechas, para detener la recurrencia nosotros necesitamos Establecer alguna condición. En este ejemplo, repetimos la recursividad para mostrar nuestros registros hasta que la fecha sea menor o igual que la fecha final.
Salida: cuando ejecutamos la consulta, podemos ver la salida a continuación.

CTE múltiple

En algunos escenarios, necesitamos crear más de una consulta CTE y unirlas para mostrar nuestro resultado. En este caso, nosotros podemos usar los CTE Múltiples. Podemos crear una consulta CTE múltiple y combinarlas en una sola consulta utilizando la coma. Múltiple CTE debe estar separado por “,” coma entrecomillado seguido por el nombre CTE.
Nosotros utilizaremos el mismo ejemplo de rango de fechas anterior para usar más de una consulta CTE, aquí podemos ver que hemos creado dos consultas CTE como CTE1 y CTE 2 para mostrar el resultado del rango de fechas tanto para CTE1 como para CTE2.
Ejemplo :
Salida: cuando ejecutamos la consulta, podemos ver la salida a continuación.

Uso de la consulta CTE para la tabla SQL

Ahora veamos cómo usar la consulta CTE para nuestros datos de la tabla del servidor SQL.
Crear base de datos: primero, creamos una base de datos para luego crear nuestra tabla
Crear tabla: ahora creamos una tabla de elementos de muestra en la base de datos previamente creada.
Insertar datos de muestra: Insertaremos algunos registros de muestra para usar en nuestra consulta CTE.
Ejemplo de CTE:
Ahora nosotros crearemos un resultado temporal simple usando la consulta CTE. Aquí en esta consulta CTE es importante ver que hemos dado el nombre de la expresión como ” itemCTE ” y hemos agregado la lista de columnas que usamos en la consulta CTE. En la consulta CTE, mostramos todos los detalles del artículo con el año.
Salida: cuando ejecutamos la consulta, podemos ver la salida a continuación.
CTE utilizando Union ALL
Consideremos a continuación que hay dos escenarios para mostrar el resultado.
  1. El primer escenario es mostrar el precio de cada artículo del año actual.
  2. El segundo escenario es incrementar el 10 % a cada precio de artículo para el próximo año.
Para esto previamente usamos la consulta CTE anterior. En esta consulta, agregamos UNION ALL y en UNION ALL Query luego hacemos un cálculo para agregar 10 % a cada precio de artículo y lo mostramos en la siguiente fila con la adición de un año.
Salida: cuando ejecutamos la consulta, podemos ver la salida a continuación.

Expresiones de tabla comunes (CTE) para inserción

Ahora veremos cómo insertar el resultado CTE en otra tabla. Para tal fin, consideremos nuestra tabla de elementos anterior. Luego insertamos el resultado de los detalles del artículo de la consulta CTE anterior en la tabla Historial del artículo. Para esto previamente creamos una tabla de historial de artículos.
Crear tabla de historial de artículos: en esta tabla de historial, nosotros agregamos las mismas columnas que la tabla de artículos junto con la columna MarketRate como precio de artículo actual o precio futuro. Aquí está la consulta para crear una tabla ItemHistory.
Ejemplo de inserción de CTE:
Aquí previamente usamos la misma consulta CTE anterior. Luego Inserte el resultado en la tabla historial de articulos. De esta consulta, insertamos los dos detalles del artículo del precio actual del artículo junto con el año siguiente. Los precios del artículo se agregaron como un 10 % más.
Salida: cuando ejecutamos la consulta, podemos ver la salida a continuación, ya que se han insertado 30 registros en nuestra tabla de historial de elementos.
Seleccionar consulta:
Para ver el resultado del historial del elemento, seleccionamos y mostramos todos los detalles.
Salida: cuando ejecutamos la consulta, podemos ver la salida a continuación de la tabla de historial de elementos.

Ejemplo de Crear una vista con CTE:

Ahora veremos cómo se puede usar la consulta CTE anterior en una vista. Aquí nosotros creamos una vista y agregamos el resultado CTE dentro de la vista. Cuando seleccionamos la vista como resultado, entonces podemos ver lo que se mostrará a la salida CTE.
Consulta de ejemplo:
Salida: cuando ejecutamos la consulta, podemos ver la salida a continuación como resultado de la vista.
Cómo escribir una consulta CTE limpia:
Aquí hay algunas pautas básicas que deben seguirse para escribir una buena consulta CTE.
  1. Un CTE debe ir seguido de una sola instrucción SELECT, INSERT, UPDATE o DELETE que haga referencia a algunas o todas las columnas CTE.
  2. Se pueden definir múltiples definiciones de consulta CTE en un CTE que sea no recursivo.
  3. Un CTE puede hacer referencia a sí mismo y a los CTE definidos previamente en la misma cláusula WITH
  4. Podemos usar solo una cláusula con un CTE
  5. Las instrucciones ORDER BY, INTO, COMPUTE o COMPUTE BY, OPTION, FOR XML no se pueden usar en la definición de consulta CTE no recursiva
  6. Las subconsultas SELECT DISTINCT, GROUP BY, HAVING, Scalar aggregation, TOP, LEFT, RIGHT, OUTER JOIN (INNER JOIN está permitida) no se pueden utilizar en una definición de consulta CTE recursiva.

Conclusión


Los CTE se pueden usar para crear una consulta recursiva y se pueden usar para hacer referencia a sí mismos en múltiples ocasiones. Los CTE se pueden usar en lugar de las vistas y, finalmente, un CTE es fácil y simple para facilitar la lectura y la mantenibilidad del código.

No hay comentarios.:

Publicar un comentario

Dejanos tu comentario para seguir mejorando!

Post Top Ad

Your Ad Spot

Páginas