
Resumen: en este tutorial, aprenderás sobre la expresión de tabla común o CTE
en SQL Server mediante el uso de la cláusula WITH
.
Introducción a CTE en SQL Server
CTE
significa expresión de tabla común. Una CTE
permite definir un conjunto de resultados con nombre temporal que está disponible temporalmente en el ámbito de ejecución de una declaración como SELECT, INSERT, UPDATE, DELETE, o MERGE.
A continuación, se muestra la sintaxis común de un CTE
en SQL Server:
WITH expression_name[(column_name [,...])]
AS
(CTE_definition)
SQL_statement;
En esta sintaxis:
- Primero, se especifica el nombre de la expresión (
expression_name
) al que se puede hacer referencia más adelante en una consulta. - A continuación, se especifica una lista de columnas separadas por comas después de
expression_name
. El número de columnas debe ser el mismo que el número de columnas definido enCTE_definition
. - Luego, se use la palabra clave
AS
después del nombre de la expresión o la lista de columnas si se especifica la lista de columnas. - Después, se define una instrucción
SELECT
cuyo conjunto de resultados completa la expresión de la tabla común. - Finalmente, se consulta la expresión de tabla común en una consulta (
instruccion_SQL
) como un SELECT, INSERT, UPDATE, DELETE, o MERGE.
Preferimos usar las expresiones de tabla comunes en lugar de usar subconsultas porque las expresiones de tabla comunes son más legibles. También usamos CTE
en las consultas que contienen funciones analíticas
Ejemplos de CTE en SQL Server
Tomemos algunos ejemplos del uso de expresiones de tabla comunes.
A) Ejemplo simple de CTE de SQL Server
Esta consulta utiliza un CTE
para devolver los montos de ventas por parte del personal de ventas en 2018 de la base de datos de pruebas:
WITH cte_sales_amounts (staff, sales, year) AS (
SELECT
first_name + ' ' + last_name,
SUM(quantity * list_price * (1 - discount)),
YEAR(order_date)
FROM
sales.orders o
INNER JOIN sales.order_items i ON i.order_id = o.order_id
INNER JOIN sales.staffs s ON s.staff_id = o.staff_id
GROUP BY
first_name + ' ' + last_name,
year(order_date)
)
SELECT
staff,
sales
FROM
cte_sales_amounts
WHERE
year = 2018;
Resultado:
staff | sales |
---|---|
Genna Serrano | 247174.3531 |
Kali Vargas | 135113.1647 |
Layla Terrell | 56531.3358 |
Marcelene Boyer | 520105.6064 |
Mireya Copeland | 230246.9328 |
Venita Daniel | 625358.3947 |
En este ejemplo:
- Primero, definimos cte_sales_amounts como el nombre de la expresión de tabla común. el
CTE
devuelve un resultado que consta de tres columnasstaff
,year
ysales
derivadas de la consulta de definición. - En segundo lugar, construimos una consulta que devuelve el monto total de ventas por personal de ventas y año consultando los datos de las tablas
orders
,order_items
ystaffs
. - Tercero, nos referimos al
CTE
en la consulta externa y seleccionamos solo las filas cuyo año sea 2018.
Ten en cuenta que este ejemplo es únicamente para fines de demostración para ayudar a comprender gradualmente cómo funcionan las expresiones de tabla comunes. Hay una forma más óptima de lograr el resultado sin usar CTE
.
B) Usar una expresión de tabla común para hacer informes de promedios basados en conteos
Este ejemplo usa el CTE
para devolver la cantidad promedio de pedidos de ventas en 2018 para todo el personal de ventas.
WITH cte_sales AS (
SELECT
staff_id,
COUNT(*) order_count
FROM
sales.orders
WHERE
YEAR(order_date) = 2018
GROUP BY
staff_id
)
SELECT
AVG(order_count) average_orders_by_staff
FROM
cte_sales;
Resultado:
average_orders_by_staff
-----------------------
48
(1 row affected)
En este ejemplo:
Primero, usamos cte_sales
como el nombre de la expresión de tabla común. Omitimos la lista de columnas del CTE
para que se derive de la declaración de definición de CTE
. En este ejemplo, incluye las columnas staff_id
y order_count
.
En segundo lugar, usamos la siguiente consulta para definir el conjunto de resultados que completa la expresión de tabla común cte_sales
. La consulta devuelve el número de pedidos en 2018 por parte del personal de ventas.
SELECT
staff_id,
COUNT(*) order_count
FROM
sales.orders
WHERE
YEAR(order_date) = 2018
GROUP BY
staff_id;
En tercer lugar, nos referimos a cte_sales en la declaración externa y usamos la función AVG() para obtener el pedido de venta promedio de todo el personal.
SELECT
AVG(order_count) average_orders_by_staff
FROM
cte_sales;
C) Usar múltiples CTE de SQL Server en un solo ejemplo de consulta
El siguiente ejemplo utiliza dos CTE cte_category_counts
y cte_category_sales
para devolver el número de productos y ventas para cada categoría de producto. La consulta externa une dos CTE
mediante la columna category_id
WITH cte_category_counts (
category_id,
category_name,
product_count
)
AS (
SELECT
c.category_id,
c.category_name,
COUNT(p.product_id)
FROM
production.products p
INNER JOIN production.categories c
ON c.category_id = p.category_id
GROUP BY
c.category_id,
c.category_name
),
cte_category_sales(category_id, sales) AS (
SELECT
p.category_id,
SUM(i.quantity * i.list_price * (1 - i.discount))
FROM
sales.order_items i
INNER JOIN production.products p
ON p.product_id = i.product_id
INNER JOIN sales.orders o
ON o.order_id = i.order_id
WHERE order_status = 4 -- completed
GROUP BY
p.category_id
)
SELECT
c.category_id,
c.category_name,
c.product_count,
s.sales
FROM
cte_category_counts c
INNER JOIN cte_category_sales s
ON s.category_id = c.category_id
ORDER BY
c.category_name;
Resultado:
category_id | category_name | product_count | sales |
---|---|---|---|
1 | Children Bicycles | 59 | 259986.3712 |
2 | Comfort Bicycles | 30 | 346449.9962 |
3 | Cruisers Bicycles | 78 | 866524.4023 |
4 | Cyclocross Bicycles | 10 | 642584.8253 |
5 | Electric Bikes | 24 | 733493.7451 |
6 | Mountain Bikes | 60 | 2486420.2641 |
7 | Road Bikes | 60 | 1327155.6399 |
En este tutorial, aprendiste a usar expresiones de tabla comunes o CTE
en SQL Server para construir consultas complejas de una manera fácil de entender.