mysql - COALESCE with SUM with Join table 计算不正确

Question

我正在使用 5 个不同表的联合处理联接表因为我已经为所有表的联合编写了代码，并且它在没有联接表的情况下工作正常

如您所见

    SELECT  sku,
        sum(units_ordered) as units_ordered
FROM   (
        SELECT sku, units_ordered
        FROM   tbl_sku_units_order 
        UNION ALL
        SELECT sku, units_ordered
        FROM   tbl_sku_units_order_de
        UNION ALL
        SELECT sku, units_ordered
        FROM   tbl_sku_units_order_es
        UNION ALL
        SELECT sku, units_ordered
        FROM   tbl_sku_units_order_fr
        UNION ALL
        SELECT sku, units_ordered
        FROM   tbl_sku_units_order_it
       ) AllMarketplace
group by sku
ORDER BY units_ordered DESC;

正如您在 db fiddle 的第一个链接中看到的结果

https://www.db-fiddle.com/f/nbGcxACaf4doHRssJDSyRN/0结果是正确的

您可以看到“H-Root-M012S-Black 87”，这是正确的总数量库存。

我需要他们与其他表之一作为左连接加入。这是 db fiddle 中的代码

https://www.db-fiddle.com/f/dxqsU4enjRYqk7du7VEB4d/0

这是我写的代码

SELECT 
    ls.sku                                  AS list_sku,
    COALESCE(MIN(suo.sku), 'Not Sold')      AS sold_sku,
    COALESCE(SUM(suo.units_ordered), 0)     AS units_ordered
FROM
    (SELECT 
        COALESCE(MIN(sku), 'Not Sold')      AS sold_sku,
        COALESCE(SUM(units_ordered), 0)     AS units_ordered
        FROM   tbl_sku_units_order 
        UNION ALL
        SELECT  
        COALESCE(MIN(sku), 'Not Sold')      AS sold_sku,
        COALESCE(SUM(units_ordered), 0)     AS units_ordered
        FROM   tbl_sku_units_order_de
        UNION ALL
        SELECT 
        COALESCE(MIN(sku), 'Not Sold')      AS sold_sku,
        COALESCE(SUM(units_ordered), 0)     AS units_ordered
        FROM   tbl_sku_units_order_es
        UNION ALL
        SELECT 
        COALESCE(MIN(sku), 'Not Sold')      AS sold_sku,
        COALESCE(SUM(units_ordered), 0)     AS units_ordered
        FROM   tbl_sku_units_order_fr
        UNION ALL
        SELECT  
        COALESCE(MIN(sku), 'Not Sold')      AS sold_sku,
        COALESCE(SUM(units_ordered), 0)     AS units_ordered
        FROM   tbl_sku_units_order_it
    ) as t1,
    tbl_list_sku AS ls
        LEFT JOIN
    tbl_sku_units_order AS suo ON suo.sku = ls.sku
GROUP BY ls.sku
ORDER BY units_ordered DESC;

它返回正常，但计算错误

我怀疑是因为组问题？我必须在组上对其进行调整，但所有结果都返回了相同的结果以及右连接表或内连接表。

标签： mysqlworkbench

Answer 1

你的 SQL 应该是这样的：

SELECT 
    ls.sku                                  AS list_sku,
    COALESCE(MIN(suo.sku), 'Not Sold')      AS sold_sku,
    COALESCE(SUM(suo.units_ordered), 0)     AS units_ordered
FROM
    (SELECT sku,
        COALESCE(MIN(sku), 'Not Sold')      AS sold_sku,
        COALESCE(SUM(units_ordered), 0)     AS units_ordered
        FROM   tbl_sku_units_order 
        UNION ALL
        SELECT sku,  
        COALESCE(MIN(sku), 'Not Sold')      AS sold_sku,
        COALESCE(SUM(units_ordered), 0)     AS units_ordered
        FROM   tbl_sku_units_order_de
        UNION ALL
        SELECT sku,
        COALESCE(MIN(sku), 'Not Sold')      AS sold_sku,
        COALESCE(SUM(units_ordered), 0)     AS units_ordered
        FROM   tbl_sku_units_order_es
        UNION ALL
        SELECT sku,
        COALESCE(MIN(sku), 'Not Sold')      AS sold_sku,
        COALESCE(SUM(units_ordered), 0)     AS units_ordered
        FROM   tbl_sku_units_order_fr
        UNION ALL
        SELECT  sku,
        COALESCE(MIN(sku), 'Not Sold')      AS sold_sku,
        COALESCE(SUM(units_ordered), 0)     AS units_ordered
        FROM   tbl_sku_units_order_it
    ) as t1 join 
    tbl_list_sku AS ls on t1.sku = ls.sku
        LEFT JOIN
    tbl_sku_units_order AS suo ON suo.sku = ls.sku
GROUP BY ls.sku
ORDER BY units_ordered DESC;

编辑-1

SELECT 
    ls.sku                                  AS list_sku,
    COALESCE(MIN(suo.sku), 'Not Sold')      AS sold_sku,
    COALESCE(SUM(suo.units_ordered), 0)     AS units_ordered
FROM tbl_list_sku AS ls left join
    (SELECT sku,
        COALESCE(MIN(sku), 'Not Sold')      AS sold_sku,
        COALESCE(SUM(units_ordered), 0)     AS units_ordered
        FROM   tbl_sku_units_order 
        UNION ALL
        SELECT sku,  
        COALESCE(MIN(sku), 'Not Sold')      AS sold_sku,
        COALESCE(SUM(units_ordered), 0)     AS units_ordered
        FROM   tbl_sku_units_order_de
        UNION ALL
        SELECT sku,
        COALESCE(MIN(sku), 'Not Sold')      AS sold_sku,
        COALESCE(SUM(units_ordered), 0)     AS units_ordered
        FROM   tbl_sku_units_order_es
        UNION ALL
        SELECT sku,
        COALESCE(MIN(sku), 'Not Sold')      AS sold_sku,
        COALESCE(SUM(units_ordered), 0)     AS units_ordered
        FROM   tbl_sku_units_order_fr
        UNION ALL
        SELECT  sku,
        COALESCE(MIN(sku), 'Not Sold')      AS sold_sku,
        COALESCE(SUM(units_ordered), 0)     AS units_ordered
        FROM   tbl_sku_units_order_it
    ) as t1 on t1.sku = ls.sku
        LEFT JOIN
    tbl_sku_units_order AS suo ON suo.sku = ls.sku
GROUP BY ls.sku
ORDER BY units_ordered DESC;