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.

WITH expression_name [ ( column_name [,...n] ) ]

( CTE_query_definition )

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

Select [Column1,Column2,Column3 …..] from expression_name

Select * from expression_name

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.

;with ROWCTE(ROWNO) as

(

SELECT

ROW_NUMBER() OVER(ORDER BY name ASC) AS ROWNO

FROM sys.databases

WHERE database_id <= 10

)

SELECT * FROM ROWCTE

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.

Declare @RowNo int =1;

;with ROWCTE as

(

SELECT @RowNo as ROWNO

UNION ALL

SELECT ROWNO+1

FROM ROWCTE

WHERE RowNo < 10

)

SELECT * FROM ROWCTE

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.

declare @startDate datetime,

@endDate datetime;

select @startDate = getdate(),

@endDate = getdate()+16;

-- select @sDate StartDate,@eDate EndDate

;with myCTE as

(

select 1 as ROWNO,@startDate StartDate,'W - '+convert(varchar(2),

DATEPART( wk, @startDate))+' / D ('+convert(varchar(2),@startDate,106)+')' as 'WeekNumber'

)

select ROWNO,Convert(varchar(10),StartDate,105) as StartDate ,WeekNumber from myCTE ;

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.

declare @startDate datetime,

@endDate datetime;

select @startDate = getdate(),

@endDate = getdate()+16;

-- select @sDate StartDate,@eDate EndDate

;with myCTE as

(

select 1 as ROWNO,@startDate StartDate,'W - '+convert(varchar(2),

DATEPART( wk, @startDate))+' / D ('+convert(varchar(2),@startDate,106)+')' as 'WeekNumber'

union all

select ROWNO+1 ,dateadd(DAY, 1, StartDate) ,

'W - '+convert(varchar(2),DATEPART( wk, StartDate))+' / D ('+convert(varchar(2),

dateadd(DAY, 1, StartDate),106)+')' as 'WeekNumber'

FROM myCTE

WHERE dateadd(DAY, 1, StartDate)<= @endDate

)

select ROWNO,Convert(varchar(10),StartDate,105) as StartDate ,WeekNumber from myCTE

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 :

Declare @startDate datetime,@endDate datetime;

Declare @startDate1 datetime,@endDate1 datetime;

Set @startDate = '2017-02-10'; Set @endDate = '2017-02-15';

Set @startDate1 = '2017-02-16'; Set @endDate1 = '2017-02-28';

WITH CTE1

AS (

SELECT 'CTE1' CTEType ,@startDate StartDate,'W'+convert(varchar(2),DATEPART( wk, @startDate))+'('+convert(varchar(2),@startDate,106)+')' as 'WeekNumber'

UNION ALL

SELECT CTEType, dateadd(DAY, 1, StartDate) ,'W'+convert(varchar(2),DATEPART( wk, StartDate))+'('+convert(varchar(2),dateadd(DAY, 1, StartDate),106)+')' as 'WeekNumber'

FROM CTE1

WHERE dateadd(DAY, 1, StartDate)<= @endDate

CTE2

AS (

SELECT 'CTE2' CTEType, @startDate StartDate,'W'+convert(varchar(2),DATEPART( wk, @startDate1))+'('+convert(varchar(2),@startDate1,106)+')' as 'WeekNumber'

UNION ALL

SELECT 'CTE2' valuetype, dateadd(DAY, 1, StartDate) ,'W'+convert(varchar(2),DATEPART( wk, StartDate))+'('+convert(varchar(2),dateadd(DAY, 1, StartDate),106)+')' as 'WeekNumber'

FROM CTE2

WHERE dateadd(DAY, 1, StartDate)<= @endDate1

)

SELECT CTEType, Convert(varchar(10),StartDate,105) as StartDate ,WeekNumber FROM CTE1

UNION ALL

SELECT CTEType, Convert(varchar(10),StartDate,105) as StartDate ,WeekNumber FROM CTE2

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

USE MASTER

-- 1) Check for the Database Exists .If the database is exist then drop and create new DB

IF EXISTS (SELECT [name] FROM sys.databases WHERE [name] = 'CTEDB' )

DROP DATABASE CTEDB

CREATE DATABASE CTEDB

USE CTEDB

Crear tabla: ahora creamos una tabla de elementos de muestra en la base de datos previamente creada.

IF EXISTS ( SELECT [name] FROM sys.tables WHERE [name] = 'ItemDetails' )

DROP TABLE ItemDetails

CREATE TABLE ItemDetails

(

Item_ID int identity(1,1),

Item_Name VARCHAR(100) NOT NULL,

Item_Price int NOT NULL,

Date VARCHAR(100) NOT NULL ,

CONSTRAINT [PK_ItemDetails] PRIMARY KEY CLUSTERED

(

[Item_ID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

Insertar datos de muestra: Insertaremos algunos registros de muestra para usar en nuestra consulta CTE.

Insert into ItemDetails(Item_Name,Item_Price,Date) values('Access Point',950,'2017-02-10')

Insert into ItemDetails(Item_Name,Item_Price,Date) values('CD',350,'2017-02-13')

Insert into ItemDetails(Item_Name,Item_Price,Date) values('Desktop Computer',1400,'2017-02-16')

Insert into ItemDetails(Item_Name,Item_Price,Date) values('DVD',1390,'2017-03-05')

Insert into ItemDetails(Item_Name,Item_Price,Date) values('DVD Player',450,'2017-05-07')

Insert into ItemDetails(Item_Name,Item_Price,Date) values('Floppy',1250,'2017-05-07')

Insert into ItemDetails(Item_Name,Item_Price,Date) values('HDD',950,'2017-07-10')

Insert into ItemDetails(Item_Name,Item_Price,Date) values('MobilePhone',1150,'2017-07-10')

Insert into ItemDetails(Item_Name,Item_Price,Date) values('Mouse',399,'2017-08-12')

Insert into ItemDetails(Item_Name,Item_Price,Date) values('MP3 Player ',897,'2017-08-14')

Insert into ItemDetails(Item_Name,Item_Price,Date) values('Notebook',750,'2017-08-16')

Insert into ItemDetails(Item_Name,Item_Price, Date) values('Printer',675,'2017-07-18')

Insert into ItemDetails(Item_Name,Item_Price,Date) values('RAM',1950,'2017-09-23')

Insert into ItemDetails(Item_Name,Item_Price,Date) values('Smart Phone',679,'2017-09-10')

Insert into ItemDetails(Item_Name,Item_Price,Date) values('USB',950,'2017-02-26')

select * from ItemDetails

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.

;WITH itemCTE (Item_ID, Item_Name, Item_Price,SalesYear)

(

SELECT Item_ID, Item_Name, Item_Price ,YEAR(Date) SalesYear

FROM ItemDetails

)

Select * from itemCTE

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.

El primer escenario es mostrar el precio de cada artículo del año actual.
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.

;WITH itemCTE (Item_ID, Item_Name, Item_Price,MarketRate,SalesYear)

(

SELECT Item_ID, Item_Name, Item_Price ,'Present Price' as MarketRate,YEAR(Date) as SalesYear

FROM ItemDetails

UNION ALL

SELECT Item_ID as Item_ID, Item_Name,

(Item_Price + (Item_Price *10 )/100) as Item_Price,

'Future Price' as MarketRate, YEAR(dateadd(YEAR, 1, Date)) as SalesYear

FROM ItemDetails

)

SELECT * from itemCTE Order by Item_Name,SalesYear

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.

CREATE TABLE ItemHistory

(

ID int identity(1,1),

oldITEMID int,

Item_Name VARCHAR(100) NOT NULL,

Item_Price int NOT NULL,

MarketRate VARCHAR(100) NOT NULL,

Date VARCHAR(100) NOT NULL )

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.

;WITH itemCTE (Item_ID, Item_Name, Item_Price,MarketRate,Date)

(

SELECT Item_ID, Item_Name, Item_Price ,'Present Price' as MarketRate,Date

FROM ItemDetails

UNION ALL

SELECT Item_ID as Item_ID, Item_Name,(Item_Price + (Item_Price *10 )/100) as Item_Price,

'Future Price' as MarketRate, dateadd(YEAR, 1, Date) as Date

FROM ItemDetails

)

-- Define the outer query referencing the CTE name.

Insert into ItemHistory(oldITEMID ,Item_Name,Item_Price,MarketRate,Date)

SELECT Item_ID, Item_Name, Item_Price,MarketRate,year(Date) from itemCTE Order by Item_Name,Date

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.

select * from ItemHistory

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:

CREATE VIEW CTEVIEW

WITH itemCTE1 AS

(

SELECT Item_ID, Item_Name, Item_Price ,'Present Price' as MarketRate,Date as IDate

FROM ItemDetails

UNION ALL

SELECT Item_ID as Item_ID, Item_Name,(Item_Price + (Item_Price *10 )/100) as Item_Price,

'Future Price' as MarketRate, dateadd(YEAR, 1, Date) as IDate

FROM ItemDetails

)

SELECT Item_ID, Item_Name, Item_Price,MarketRate,year(IDate) as IDate from itemCTE1

-- T-SQL test view

SELECT * FROM CTEVIEW Order by Item_Name,IDate

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.

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.
Se pueden definir múltiples definiciones de consulta CTE en un CTE que sea no recursivo.
Un CTE puede hacer referencia a sí mismo y a los CTE definidos previamente en la misma cláusula WITH
Podemos usar solo una cláusula con un CTE
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
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.

Acerca de
Últimas entradas

Syed Shanu

Syed Shanu is a Microsoft MVP, two-time CsharpCorner MVP and two-time Code project MVP, Author, Blogger, Speaker and always happy to share what he knows to others.

He’s basically from Madurai, Tamil Nadu, India. He’s working as Technical Lead in South Korea with more than 10 years of experience on Microsoft technologies.

Shanu is active in the community and always happy to share topics related to ASP.NET, MVC, ASP.NET Core, Web API, SQL Server, UWP, Azure, C#, AngularJs, Angular2. He has written more than 70 articles with various technology’s on ASP.NET, SQL Server, C#, AngularJs, Angular2, ASP.NET Core, Unity 3D, Windows Universal App and Samsung Gear App development. He’s several times TechNet Guru Gold Winner and you can found all his contribution in MSDN and also in TechNet Wiki https://social.technet.microsoft.com/profile/syedshanu/

He is also an Author of the eBook "SQL Queries For Beginners" you can download the eBook from here: http://www.c-sharpcorner.com/ebooks/sql-queries-for-beginners

View all posts by Syed Shanu

Últimas entradas de Syed Shanu (ver todo)

Expresiones comunes de tabla de SQL Server (CTE) - December 6, 2019

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

Sintaxis y ejemplos de expresiones comunes de tabla

Tipos de expresión comunes de tabla (CTE)

CTE múltiple

Uso de la consulta CTE para la tabla SQL

Expresiones de tabla comunes (CTE) para inserción

Ejemplo de Crear una vista con CTE:

Conclusión

Related posts: