时间戳

首页 > 解决方案 > 连接表或联合表

sql - 连接表或联合表

问题描述

我正在尝试在同一个查询中加入两个选择。我尝试了一个联合，但我需要将寄存器应用于同一行。

我的选择1是：

SELECT
    b.cod_articulo,
    b.nom_articulo,
    sum(a.cantidad) AS 'Cantidad',
    sum(a.cantidad * b.conv_art_prec) AS 'Total Peso',
    b.cod_uni_pesoart AS 'Unidad'
FROM
    cpf_stockaux a
LEFT OUTER JOIN
    ct_articulos b
ON
    a.cod_articulo=b.cod_articulo
WHERE
    b.cod_articulo IN ('40101','411','4112','4134','4135','4201','433','434','446','451','467','473','480','501','540','551','5510','562','5625','58201','635','5613')
AND YEAR(a.fec_doc)='2021'
AND MONTH(a.fec_doc)='05'
GROUP BY b.cod_articulo, b.nom_articulo, b.cod_uni_pesoart

该选择的结果是：

cod_articulo|nom_articulo                                       |Cantidad       |Total Peso            |Unidad
40101       |SEMOLIN (BOLSA 25 KG.)                             |17998.00000    |449950.00000000000    |Kg 
4201        |HARINA DE TRIGO 0000 PARA TAPAS KG.(BOLSA 25 KG.)  |13280.00000    |332000.00000000000    |Kg 
451         |HARINA DE TRIGO 000 CON MEJORADORES KG.A GRANEL    |1094484.88000  |1094484.88000000000   |Kg 
501         |HARINA  00 CON MEJORADORES KG. A GRANEL            |2097668.36000  |2097668.36000000000   |Kg  
551         |HARINA 0 CON MEJORADORES  KG. A GRANEL             |4859169.16000  |4859169.16000000000   |Kg 
5510        |HARINA 0 SIN ADITIVACION KG. GRANEL                |2138322.64050  |2138322.64050000000   |Kg 
5613        |AFRECHILLO DE TRIGO  KG. A GRANEL                  |6850287.25000  |6850287.25000000000   |Kg 
562         |SALVADO DE TRIGO KG.(BOLSA 25 KG.)                 |1380.00000     |34500.00000000000     |Kg 
5625        |SALVADO KG. (BOLSA 20 KG.)                         |132.00000      |2640.00000000000      |Kg 
58201       |HARINA INTEGRAL KG. (BOLSA 25 KG.)                 |64657.00000    |1616425.00000000000   |Kg

我的第二个选择：

SELECT
    c.cod_articulo,
    c.nom_articulo,
    sum(b.total) AS 'Cantidad No Conf.',
    sum(b.total * c.conv_art_prec) AS 'Total No Conf. Peso',
FROM
    cpt_parteprod b
LEFT OUTER JOIN
    ct_articulos c
ON
    b.cod_articulo=c.cod_articulo
WHERE
    c.cod_articulo IN ('40101','411','4112','4134','4135','4201','433','434','446','451','467','473','480','501','540','551','5510','562','5625','58201','635','5613')
AND b.cod_doc = 'ppro'
AND YEAR(b.fec_doc)='2021'
AND MONTH(b.fec_doc)='05'
AND YEAR(b.fec_doc)='2021'
AND MONTH(b.fec_doc)='05'
AND b.formulario = 'TprdParteEMoliNC'
GROUP BY c.cod_articulo, c.nom_articulo, c.cod_uni_pesoart

该选择的结果是：

cod_articulo|nom_articulo                                       |Cantidad No Conf.  |Total No Conf. Peso      |Unidad
40101       |SEMOLIN (BOLSA 25 KG.)                             |6.00000            |150.00000000000          |Kg             
4201        |HARINA DE TRIGO 0000 PARA TAPAS KG.(BOLSA 25 KG.)  |189.00000          |4725.00000000000         |Kg             
58201       |HARINA INTEGRAL KG. (BOLSA 25 KG.)                 |3.00000            |75.00000000000           |Kg

而我正在尝试做的事情：

cod_articulo|nom_articulo                                       |Unidad         |Total                 |Unidad |Cantidad No Conf.  |Total No Conf. Peso  |Unidad
40101       |SEMOLIN (BOLSA 25 KG.)                             |17998.00000    |449950.00000000000    |Kg     |6.00000            |150.00000000000      |Kg
4201        |HARINA DE TRIGO 0000 PARA TAPAS KG.(BOLSA 25 KG.)  |13280.00000    |332000.00000000000    |Kg     |189.00000          |4725.00000000000     |Kg
451         |HARINA DE TRIGO 000 CON MEJORADORES KG.A GRANEL    |1094484.88000  |1094484.88000000000   |Kg     |0                  |0                    |Kg
501         |HARINA  00 CON MEJORADORES KG. A GRANEL            |2097668.36000  |2097668.36000000000   |Kg     |0                  |0                    |Kg
551         |HARINA 0 CON MEJORADORES  KG. A GRANEL             |4859169.16000  |4859169.16000000000   |Kg     |0                  |0                    |Kg
5510        |HARINA 0 SIN ADITIVACION KG. GRANEL                |2138322.64050  |2138322.64050000000   |Kg     |0                  |0                    |Kg
5613        |AFRECHILLO DE TRIGO  KG. A GRANEL                  |6850287.25000  |6850287.25000000000   |Kg     |0                  |0                    |Kg
562         |SALVADO DE TRIGO KG.(BOLSA 25 KG.)                 |1380.00000     |34500.00000000000     |Kg     |0                  |0                    |Kg
5625        |SALVADO KG. (BOLSA 20 KG.)                         |132.00000      |2640.00000000000      |Kg     |0                  |0                    |Kg
58201       |HARINA INTEGRAL KG. (BOLSA 25 KG.)                 |64657.00000    |1616425.00000000000   |Kg     |3.00000            |75.00000000000       |Kg

对于我有 0 的情况，很明显我使用了 CASE

标签： sqljoinunion

解决方案

执行此联接的最佳方法是使用“FULL OUTER JOIN”图像解释完全外部联接，因为您需要两个数据的完整信息，在这种情况下您可以使用以下代码：

Select 
    A.cod_articulo,
    A.nom_articulo,
    A.Cantidad,
    A.Total_Peso,
    A.Unidad,
    B.Cantidad_No_Conf,
    B.Total_No_Conf_Peso

from (SELECT
        b.cod_articulo,
        b.nom_articulo,
        sum(a.cantidad) AS 'Cantidad',
        sum(a.cantidad * b.conv_art_prec) AS Total_Peso,
        b.cod_uni_pesoart AS 'Unidad'
    FROM
        cpf_stockaux a
        LEFT OUTER JOIN
        ct_articulos b
        ON
        a.cod_articulo=b.cod_articulo
    WHERE
        b.cod_articulo IN ('40101','411','4112','4134','4135','4201','433','434','446','451','467','473','480','501','540','551','5510','562','5625','58201','635','5613')
        AND YEAR(a.fec_doc)='2021'
        AND MONTH(a.fec_doc)='05'
    GROUP BY b.cod_articulo, b.nom_articulo, b.cod_uni_pesoart) as A

FULL OUTER JOIN

    (SELECT
        c.cod_articulo,
        c.nom_articulo,
        sum(b.total) AS Cantidad_No_Conf,
        sum(b.total * c.conv_art_prec) AS Total_No_Conf_Peso, 
    FROM
        cpt_parteprod b
        LEFT OUTER JOIN
        ct_articulos c
        ON
        b.cod_articulo=c.cod_articulo
    WHERE
        c.cod_articulo IN ('40101','411','4112','4134','4135','4201','433','434','446','451','467','473','480','501','540','551','5510','562','5625','58201','635','5613')
        AND b.cod_doc = 'ppro'
        AND YEAR(b.fec_doc)='2021'
        AND MONTH(b.fec_doc)='05'
        AND YEAR(b.fec_doc)='2021'
        AND MONTH(b.fec_doc)='05'
        AND b.formulario = 'TprdParteEMoliNC'
    GROUP BY c.cod_articulo, c.nom_articulo, c.cod_uni_pesoart) as B

ON A.cod_articulo = B.cod_articulo

推荐阅读