Principales respuestas
¿Cómo puedo hacer la siguiente consulta en SQL?

Pregunta
-
Verán, necesito obtener una tabla que resulta de combinar datos de otras tres tablas, bajo las siguientes condiciones:
TABLA A OBTENER:
ITEM....CÓDIGO.........COLOR...TOTAL
CDE..........110-235...........ROJO...........100
CDE..........310-578...........VERDE.........300
CDE..........145-140...........ROJO...........400
..........
Como se observa la columna CÓDIGO está compuesta por tres dígitos que empiezan con 110, 210, 310..., 910 o con tres dígitos cualquiera y todos los códigos terminan con otros tres dígitos cualquiera, tomando todos la forma: XYZ-UVW
.........
Las columnas ITEM, CÓDIGO y COLOR se deben obtener de la TABLA1. Ingreso un ítem, por ejemplo "CDE" y debe obtener de esa columna, para dicho ítem, los valores de código y color (CÓDIGO en TABLA1 es único por cada ITEM, no se repite):
TABLA1
ITEM.......CÓDIGO.......COLOR
CDE..........110-235.....ROJO
CDE..........310-578.....VERDE
CDE..........145-140.....ROJO
ABC..........110-235.....ROJO
ABC..........812-146.....AZUL
ABC..........310-578.....VERDE
ABC..........145-140.....ROJO
.......
La columna TOTAL se obtiene de las TABLAS 2 y 3 de acuerdo al código. Así:
..............................................
Todos los códigos que empiezan con 110, 210, 310...., hasta 910 se encuentran en la TABLA2:
TABLA2
CODIGO........TOTAL
110-235.......30
110-235......70
110-236......85
210-222......114
310-578......100
310-578.....200
310-579....125
Se agrupan y totalizan todos los códigos iguales, como los resaltados en negrita. Por ejemplo se obtiene el código 110-235 y su total que es 100 (30+70=100).
...........................................
...el resto de códigos (los que no empiezan con 110, 210, 310...,910) se encuentran en la TABLA3 que tiene la misma estructura que TABLA2:
TABLA3
CÓDIGO........TOTAL
145-140........300
145-140........100
145-138........115
145-138........217
La columna TOTAL se obtiene igual que en el caso de la TABLA2: se agrupa por código y se totaliza. Así por ejemplo, para el código 145-140 (resaltado en negrita), su total es 400 (300+100=400).
- Editado James2016-2 lunes, 27 de mayo de 2019 16:35
Respuestas
-
Hola James2016-2:
Tú escenario, según lo has descrito.
create table tabla1 (item varchar(10), codigo varchar(10), color varchar(10)) go insert into tabla1 (item, codigo, color) values ('CDE','110-235','ROJO'), ('CDE','310-578','VERDE'), ('CDE','145-140','ROJO'), ('ABC','110-235','ROJO'), ('ABC','812-146','AZUL'), ('ABC','310-578','VERDE'), ('ABC','145-140','ROJO'); go create table tabla2(codigo varchar(10), total int) go insert into tabla2(codigo, total) values ('110-235','30'), ('110-235','70'), ('110-236','85'), ('210-222','114'), ('310-578','100'), ('310-578','200'), ('310-579','125'); go create table tabla3(codigo varchar(10), total int) go insert into tabla3(codigo, total) values ('145-140','300'), ('145-140','100'), ('145-138','115'), ('145-138','217'); go
Una posible solución.
WITH cte AS (SELECT t.item , t.codigo , t.color , t2.codigo AS codigo2 , t2.total AS total2 , t3.codigo AS codigo3 , t3.total AS total3 FROM tabla1 t LEFT JOIN tabla2 t2 ON t.codigo = t2.codigo LEFT JOIN tabla3 t3 ON t.codigo = t3.codigo), valores AS (SELECT c.codigo , c.color , c.item , COALESCE(c.total2, c.total3, 0) AS total FROM cte c) SELECT v.item , v.codigo , v.color , SUM(v.total) AS Total FROM valores v GROUP BY v.item , v.codigo , v.color;
Defines una tabla de expresión común, y la relacionas con la tabla 2 y con la tabla 3, en la salida del primer conjunto recoges el campo total, utilizando coalesce, que te devolverá el primer elemento que no sea nulo.
y en la salida de este conjunto, haces una suma agrupada.
Se puede ahorrar un paso sin dificultad, pero creo que de este modo es más didáctico.
Salida
item codigo color Total
---------- ---------- ---------- -----------
ABC 110-235 ROJO 100
ABC 145-140 ROJO 400
ABC 310-578 VERDE 300
ABC 812-146 AZUL 0
CDE 110-235 ROJO 100
CDE 145-140 ROJO 400
CDE 310-578 VERDE 300(7 filas afectadas)
Como no están ordenados los datos, a la salida del conjunto valores, pueden no encajar con lo que tu deseas. Sólo tendrías que utilizar order by y las columnas por las que ordenar.
Tabla de expresión común
https://javifer2.blogspot.com/search/label/tablas%20de%20expresión%20común%20%281%29
Coalesce
- Marcado como respuesta James2016-2 martes, 28 de mayo de 2019 6:56
-
Hola James2016-2:
Ordenación: No. En Sql no existe forma de saber el orden de los datos, salvo que utilices order by. Suele ocurrir lo que comentas, pero no funciona así. El motor, obtiene los datos, en función de lo que le resulte más rápido, por tanto la única manera de predecir la ordenación es utilizando order by.
Tabla de expresión, memoria=Si.
Parametro: Por ejemplo
tabla1 t LEFT JOIN tabla2 t2 ON t.codigo = t2.codigo LEFT JOIN tabla3 t3 ON t.codigo = t3.codigo
Where t.item = @elParametro),
Vb.Net: Explicito como lo has dicho.
- Marcado como respuesta James2016-2 martes, 28 de mayo de 2019 6:56
Todas las respuestas
-
Utiliza un select con alias.
ejemplo: SELECT Tbl1.ITEM, Tbl1.CODIGO, Tbl1.COLOR, Tbl3.TOTAL FROM Tabla1 as Tbl1, Tabla2 as Tbl2 , Tabla3 as Tbl3
con respecto a esto:
Todos los códigos que empiezan con 110, 210, 310...., hasta 910 se encuentran en la TABLA2:
podrias utilizar un case when en el campo CODIGO para traer el dato correspondiente de la tabla segun la numeracion.
-
Hola James2016-2:
Tú escenario, según lo has descrito.
create table tabla1 (item varchar(10), codigo varchar(10), color varchar(10)) go insert into tabla1 (item, codigo, color) values ('CDE','110-235','ROJO'), ('CDE','310-578','VERDE'), ('CDE','145-140','ROJO'), ('ABC','110-235','ROJO'), ('ABC','812-146','AZUL'), ('ABC','310-578','VERDE'), ('ABC','145-140','ROJO'); go create table tabla2(codigo varchar(10), total int) go insert into tabla2(codigo, total) values ('110-235','30'), ('110-235','70'), ('110-236','85'), ('210-222','114'), ('310-578','100'), ('310-578','200'), ('310-579','125'); go create table tabla3(codigo varchar(10), total int) go insert into tabla3(codigo, total) values ('145-140','300'), ('145-140','100'), ('145-138','115'), ('145-138','217'); go
Una posible solución.
WITH cte AS (SELECT t.item , t.codigo , t.color , t2.codigo AS codigo2 , t2.total AS total2 , t3.codigo AS codigo3 , t3.total AS total3 FROM tabla1 t LEFT JOIN tabla2 t2 ON t.codigo = t2.codigo LEFT JOIN tabla3 t3 ON t.codigo = t3.codigo), valores AS (SELECT c.codigo , c.color , c.item , COALESCE(c.total2, c.total3, 0) AS total FROM cte c) SELECT v.item , v.codigo , v.color , SUM(v.total) AS Total FROM valores v GROUP BY v.item , v.codigo , v.color;
Defines una tabla de expresión común, y la relacionas con la tabla 2 y con la tabla 3, en la salida del primer conjunto recoges el campo total, utilizando coalesce, que te devolverá el primer elemento que no sea nulo.
y en la salida de este conjunto, haces una suma agrupada.
Se puede ahorrar un paso sin dificultad, pero creo que de este modo es más didáctico.
Salida
item codigo color Total
---------- ---------- ---------- -----------
ABC 110-235 ROJO 100
ABC 145-140 ROJO 400
ABC 310-578 VERDE 300
ABC 812-146 AZUL 0
CDE 110-235 ROJO 100
CDE 145-140 ROJO 400
CDE 310-578 VERDE 300(7 filas afectadas)
Como no están ordenados los datos, a la salida del conjunto valores, pueden no encajar con lo que tu deseas. Sólo tendrías que utilizar order by y las columnas por las que ordenar.
Tabla de expresión común
https://javifer2.blogspot.com/search/label/tablas%20de%20expresión%20común%20%281%29
Coalesce
- Marcado como respuesta James2016-2 martes, 28 de mayo de 2019 6:56
-
Gracias, está bastante didáctico.
Se me fue ese dato, efectivamente las columnas están ordenadas, las tablas tienen índices definidos en base al código. En el caso de Tabla1, Código es una llave primaria (PK) y en el caso de las otras tablas (2 y 3) son índices.
Algunas consultas: Como las tablas ya tienen índices definidos, durante el proceso, el motor de base de datos, toma automáticamente los datos ya ordenados ¿noo?
Otra, ¿si no entendí mal, esa tabla de expresión común se crea nada más en memoria?
El resultado final está bien, sólo que no deseo obtener todos los ítems, sino uno específico que ingrese, por ejemplo, si ingreso "ABC" que traiga las filas con ABC y si ingreso "CDE" que traiga sólo las filas con CDE. ¿Cómo le especificaría ese parámetro?
Y una última cosita, para ponerlo en código de VB Net como una cadena de consulta SQL, basta con entrecomillar el código anterior? (claro, sin incluir los datos de prueba) o ¿habría que hacer algo más?
- Editado James2016-2 lunes, 27 de mayo de 2019 21:42
-
Hola James2016-2:
Ordenación: No. En Sql no existe forma de saber el orden de los datos, salvo que utilices order by. Suele ocurrir lo que comentas, pero no funciona así. El motor, obtiene los datos, en función de lo que le resulte más rápido, por tanto la única manera de predecir la ordenación es utilizando order by.
Tabla de expresión, memoria=Si.
Parametro: Por ejemplo
tabla1 t LEFT JOIN tabla2 t2 ON t.codigo = t2.codigo LEFT JOIN tabla3 t3 ON t.codigo = t3.codigo
Where t.item = @elParametro),
Vb.Net: Explicito como lo has dicho.
- Marcado como respuesta James2016-2 martes, 28 de mayo de 2019 6:56
-
-
Una molestia más, ahora que estaba haciendo pruebas me di cuenta que se me pasó un gran detalle y es que las tablas 2 y 3 tienen también la columna ITEM:
create table tabla1 (item varchar(10), codigo varchar(10), color varchar(10)) go insert into tabla1 (item, codigo, color) values ('CDE','110-236','ROJO'), ('CDE','110-235','ROJO'), ('CDE','310-578','VERDE'), ('CDE','310-579','VERDE'), ('CDE','210-222','AZUL'), ('CDE','145-140','ROJO'), ('ABC','110-235','ROJO'), ('ABC','110-236','ROJO'), ('ABC','812-146','AZUL'), ('ABC','310-578','VERDE'), ('ABC','145-140','ROJO'); ('ABC','','ROJO'); ('ABC','','AZUL'); go create table tabla2(item varchar(10), codigo varchar(10), total int) go insert into tabla2(item, codigo, total) values ('ABC','110-235','80'), ('ABC','110-235','50'), ('ABC','110-236','85'), ('ABC','310-578','50'), ('ABC','310-578','70'), ('CDE','110-236','45'), ('CDE','210-222','114'), ('CDE','310-578','100'), ('CDE','310-578','200'), ('CDE','310-579','125'); go create table tabla3(item varchar(10), codigo varchar(10), total int) go insert into tabla3(item, codigo, total) values ('ABC','145-140','300'), ('ABC','145-140','100'), ('ABC','812-146','105'), ('ABC','812-146','25'), ('CDE','145-140','75'), ('CDE','145-138','115'), ('CDE','145-138','217'); go
Así puede darse el caso que las tablas 2 y 3 tengan un código coincidente pero con diferente ITEM, entonces esas filas con distinto ITEM no se deben considerar para totalizar las sumas.
Por ejemplo:
('ABC','110-236','85'),
('CDE','110-236','45'),
Si ingreso como parámetro 'CDE', la tabla 2 tiene las dos filas anteriores, debe considerar la segunda fila y las otras que coinciden con el ITEM 'CDE' y ya no la primera fila porque tiene el ITEM 'ABC' (aún cuando ambas comparten el mismo código).
-----------------------------------------
También puede darse el caso que la tabla 1 tenga filas que no tienen un código (como en las dos últimas filas añadidas) y por tanto no están en las tablas 2 y 3, pero deben considerarse para añadirse a las filas resultantes y su valor sería 0, creo que ese caso es como si tuvieran un código que no existe en las otras dos tablas, esa parte me parece que sí funciona (pero lo menciono por si acaso).
-------------------------------------
- En cuanto al orden añadí al final By Order v.item
No sé si eso será suficiente para prevenir que las filas se adquieran correctamente, de no ser así, ¿cómo sería?
¿Cómo sería la nueva consulta?
- Editado James2016-2 martes, 28 de mayo de 2019 20:06