none
¿Cómo puedo hacer la siguiente consulta en SQL? RRS feed

  • 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).



    lunes, 27 de mayo de 2019 16:01

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

    https://javifer2.blogspot.com/search/label/coalesce

    • Marcado como respuesta James2016-2 martes, 28 de mayo de 2019 6:56
    lunes, 27 de mayo de 2019 19:40
  • 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
    martes, 28 de mayo de 2019 3:50

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.

    lunes, 27 de mayo de 2019 16:18
  • 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

    https://javifer2.blogspot.com/search/label/coalesce

    • Marcado como respuesta James2016-2 martes, 28 de mayo de 2019 6:56
    lunes, 27 de mayo de 2019 19:40
  • 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?



    lunes, 27 de mayo de 2019 21:39
  • 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
    martes, 28 de mayo de 2019 3:50
  • Muchas gracias.
    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? 




    martes, 28 de mayo de 2019 9:02