coalesce with sum with join table计算不正确

cgyqldqp 于 2021-06-21 发布在 Mysql

关注(0)|答案(1)|浏览(385)

我正在使用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;

它还可以，但计算是错误的

我怀疑是因为群体问题？我必须在组上调整它，但是所有结果都返回了相同的结果以及right或inner join表。

mysql workbench

来源：https://stackoverflow.com/questions/50387641/coalesce-with-sum-with-join-table-are-not-calculation-correct

1条答案

按热度按时间

aemubtdh1#

您的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;

赞(0）回复(0）举报 2021-06-21

我来回答

coalesce with sum with join table计算不正确

1条答案

编辑-1

相关问题

热门标签

最新问答