Crear CTE (Common Table Expressions) en SQL SERVER

Buenas tardes queridos lectores, el día de hoy les traigo un post sobre un tema un poco más avanzado, útil y muy interesante, se trata de los CTE, (Common Table Expressions), o lo que es lo mismo, expresiones comunes de tabla, pero, se preguntarán ¿Qué es esto?, ¿Para qué sirve?, pues bien, vamos a responder sus preguntas paso a paso.

Los CTE, funcionan para optimizar las consultas que hacemos, nos evitan la creación de subconsultas, que como ustedes saben, las consultas requieren un costo alto en el servidor, haciendo las consultas más lentas y también mas difíciles de leer, el CTE, llega para evitar que se hagan subconsultas, por lo cual, las consultas serán más rápidas y más fácil de leer y optimizar, veamos un ejemplo.



Tenemos una base de datos con todos los equipos y los partidos que se jugaron en el pasado mundial Rusia 2018, deseamos saber una tabla general, que nos indique el equipo, los partidos jugados, los partidos empatados y los partidos perdidos, las tablas son las siguientes:

Con esas tres tablas es suficiente para saber lo que necesitamos, les dejo el link del script de la base de datos aquí

Para hacer esa consulta que nos piden es necesario realizar 4 consultas, la consulta de los partidos jugados, la consulta de los partidos ganados, la consulta de los partidos empatados, y la consulta de los partidos perdidos, pero eso lo vamos a solucionar con el CTE.

WITH JJ AS

(

     SELECT  COUNT(P.EQUIPO_ID) JJ, P.equipo_id

     FROM PRONOSTICOS2.puntos P

     WHERE p.torneo_id = 77

     GROUP BY P.equipo_id

),

JG AS

(

     SELECT P.equipo_id, COUNT(P.EQUIPO_ID) AS JG

     FROM PRONOSTICOS2.puntos P

     WHERE P.puntos = 3

     AND p.torneo_id = 77

     GROUP BY P.equipo_id

    

),

JE AS

(

     SELECT P.equipo_id, COUNT(P.EQUIPO_ID) AS JE

     FROM PRONOSTICOS2.puntos P

     WHERE P.puntos = 1

     AND p.torneo_id = 77

     GROUP BY P.equipo_id

    

),

JP AS

(

     SELECT P.equipo_id, COUNT(P.EQUIPO_ID) AS JP

     FROM PRONOSTICOS2.puntos P

     WHERE P.puntos = 0

     AND p.torneo_id = 77

     GROUP BY P.equipo_id

)

SELECT E.NOMBRE, COUNT(E.nombre) AS JJ, ISNULL(JG.JG,0) JG, ISNULL(JE.JE,0) JE, ISNULL(JP.JP,0) JP

FROM pronosticos2.equipos E

INNER JOIN pronosticos2.puntos P ON P.equipo_id = E.ID

INNER JOIN JJ ON JJ.equipo_id = P.EQUIPO_ID

LEFT join JG JG ON JG.equipo_id = E.ID

LEFT JOIN JE JE ON JE.equipo_id = E.id

LEFT JOIN JP JP ON JP.equipo_id = E.id

WHERE P.torneo_id =77

GROUP BY E.nombre, JG.equipo_id, JG.JG , JE.JE, JP.JP

ORDER BY JJ DESC, JG DESC, JE DESC ,JP ASC

 

Si se fijan, para empezar a construir el CTE, siempre se iniciará con la palabra reservada WITH, después el nombre de la tabla y la sentencia AS, en este caso el primer CTE se llama JJ, lo cual, creará una tabla llamada JJ para poder hacer la subconsulta después, hay otra tabla llamada JG, otra tabla llamada JE y otra tabla llamada JP, estas consultas convertidas en tablas, nos van a servir para realizar los JOINS, como si las tablas en realidad existieran, pero solo existen mientras se ejecuta el query.

El resultado que nos arroja esa consulta será el siguiente:

Como pueden ver, nos retorna el resultado que estábamos buscando, por ejemplo Francia, todos sabemos que gano 6 partidos, únicamente empato con Dinamarca en fase de grupos. 

México ubicado en el 10 lugar, también es correcto, pues le gano a Alemania, a Corea, perdió con Suecia y perdió con Brasil. 

Si descargan el script que les dejé, se les hará mas sencillo comprender este ejercicio.

Utilizando los CTE, como ven, es más fácil las consultas, son más profesionales, más fácil de optimizar o dar mantenimiento, además de ser la manera correcta de hacerlo.

Hasta aquí el post queridos lectores, espero haya sido de su agrado y de su utilidad, cualquier duda, estoy a la orden. 

Espero comaprtan, comenten, den +1, o clic a algún anuncio que les interese.

Saludos