时间戳

首页 > 解决方案 > MYSQL JOIN 和 SUM - 我做错了什么？

mysql - MYSQL JOIN 和 SUM - 我做错了什么？

问题描述

我已经用尽了所有的选择。我不明白我哪里出了问题。请帮我。我不能接受更多的头撞。这就是我所在的地方...

下面是我的表结构的解释。

location_management表
包含将locations_management_id、season_id、location_id 和其他不需要的信息链接在一起的数据

orders表
包含有关订单的数据，包括 location_management_id 和 order_id 以及其他不需要的信息

orders_products表
产品数据通过 order_id 链接到订单。此表仅使用以下列：order_product_id、order_id、product_id、piece_qty

orders_adjustments表
用于跟踪对 inv_shipped 的任何调整。此表使用 order_id、product_id、piece_qty 列

这就是我今天所处的位置。下面的查询从上面的表中提取数据。

基本上，我要求从位置管理表 WHERE season_id = 12 AND location_id = 35 中获取 location_management_id。可以有多个适合 season_id 和 location_id 的可能 location_management_id。然后我需要找到与这些 location_management_id(s) 匹配的订单。找到订单后，我需要使用 order_id(s) 在 orders_products 表中查找与其关联的产品。这个查询正是这样做的，但是当我进一步合并/求和piece_qty以获得总inv_shipped时，这些数字发生了疯狂的事情。

SELECT  
    locations_management.season_id,  
    locations_management.location_id,  
    orders.order_id,  
    orders_products.product_id,  
    IFNULL((orders_products.piece_qty), 0) AS inv_shipped,  
    IFNULL((orders_adjustments.piece_qty), 0) AS inv_adjustments  
FROM  
    locations_management  
    JOIN orders USING (location_management_id)  
    LEFT JOIN orders_products USING (order_id)  
    LEFT JOIN orders_adjustments ON (orders_adjustments.order_id = orders_products.order_id) AND (orders_adjustments.product_id = orders_products.product_id)  
WHERE  
    locations_management.season_id = 12 AND locations_management.location_id = 35  
GROUP BY   
    product_id, orders_products.order_id

当我运行上面的查询时，这就是我得到的......

season_id   location_id     order_id    product_id      inv_shipped     inv_adjustments
12          35              2127        1               220             0
12          35              2194        1               160             0
12          35              2127        3               312             0
12          35              2127        4               24              0
12          35              2127        5               180             0
12          35              2194        5               24              0
12          35              2127        7               144             0
12          35              2127        7               24              0

这正是我期望得到的。多个 order_id 由 product_id 分组，所有数据都是准确的。所以现在这里成了问题。我想在 product_id 匹配并具有组合的 inv_shipped 时将它们添加/求和。因此，inv_shipped 的 product_id 1 现在总计为 380。

当我从上面进行相同的查询并将 SUM 添加到 inv_shipped 和 inv_adjustments （如下所示）时，我会在下面得到这个数据输出。注意一些值是如何翻倍的，但匹配的 product_id 行也没有合并。

    IFNULL(SUM(orders_products.piece_qty), 0) AS inv_shipped,  
    IFNULL(SUM(orders_adjustments.piece_qty), 0) AS inv_adjustments  

season_id   location_id     order_id    product_id      inv_shipped     inv_adjustments
12          35              2127        1               440             0
12          35              2194        1               160             0
12          35              2127        3               624             0
12          35              2127        4               48              0
12          35              2127        5               360             0
12          35              2194        5               24              0
12          35              2127        7               288             0
12          35              2127        7               24              0

如果我只将 GROUP BY 更改为 product_id，我会得到以下数据：

    GROUP BY product_id  

season_id   location_id     order_id    product_id      inv_shipped     inv_adjustments
12          35              2127        1               600             0
12          35              2127        3               624             0
12          35              2127        4               48              0
12          35              2127        5               384             0
12          35              2127        7               312             0

同样，这些 inv_shipped 总数不正确。那么我哪里错了？

------------------------------------ 建议 ------------- ----------------------

建议使用以下查询，但 inv_shipped 的数据输出也未正确添加。

SELECT 
    locations_management.season_id,
    locations_management.location_id,
    orders.order_id,
    products.product_id,
    products.inv_shipped 
FROM
    locations_management
    JOIN (SELECT location_management_id, order_id FROM orders group by order_id) AS orders ON orders.location_management_id = locations_management.location_management_id
    JOIN (SELECT order_id, product_id, IFNULL(SUM(piece_qty), 0) AS inv_shipped FROM orders_products GROUP BY order_id, product_id) AS products ON products.order_id = orders.order_id

WHERE
    locations_management.season_id = 12 AND locations_management.location_id = 35
ORDER BY 
    product_id, order_id  
    
season_id   location_id     order_id    product_id      inv_shipped     inv_adjustments
12          35              2127        1               440             0
12          35              2194        1               160             0
12          35              2127        3               624             0
12          35              2127        4               48              0
12          35              2127        5               360             0
12          35              2194        5               24              0
12          35              2127        7               288             0
12          35              2127        7               24              0

标签： mysqljoingroup-bysum

解决方案

首先，找到问题的根源。正确信息和错误信息之间有什么变化？

让我们看一下您的第二个查询。据我所知，发生了三件事：

  1. 您已加入另一个子查询。
  2. 您已添加SUM操作。
  3. 您添加了一个GROUP BY.

嵌套步骤是尝试删除SUMand GROUP BY，如下所示：

SELECT 
    locations_management.season_id AS season_id,
    locations_management.location_id AS location_id,
    orders.order_id AS order_id,
    products.product_id AS product_id,
    products.piece_qty
FROM
    locations_management
    JOIN (SELECT location_management_id, order_id FROM orders group by order_id) AS orders ON orders.location_management_id = locations_management.location_management_id
    JOIN (SELECT order_id, product_id, piece_qty FROM orders_products) AS products ON products.order_id = orders.order_id

WHERE
    locations_management.season_id = 12 AND locations_management.location_id = 35

我假设每个都product_id将返回两行（或更多行）。这可能是因为您的第二个表格中有JOIN两行（或更多行） ；这似乎很明显，因为表的第一个子查询有. 因此，现在要快速解决此问题，您需要改为在第二个子查询内部进行。像这样的东西：order_idorders_productsordersgroup by order_idSUM

SELECT 
    locations_management.season_id AS season_id,
    locations_management.location_id AS location_id,
    orders.order_id AS order_id,
    products.product_id AS product_id,
    products.inv_shipped 
FROM
    locations_management
    JOIN (SELECT location_management_id, order_id FROM orders group by order_id) AS orders ON orders.location_management_id = locations_management.location_management_id
    JOIN (SELECT order_id, product_id, IFNULL(SUM(products.piece_qty), 0) AS inv_shipped FROM orders_products GROUP BY order_id, product_id) AS products ON products.order_id = orders.order_id

WHERE
    locations_management.season_id = 12 AND locations_management.location_id = 35;

这可能会返回正确的结果，但是我个人会这样编写查询：

SELECT lm.season_id, lm.location_id, o.order_id , p.product_id, p.inv_shipped 
FROM locations_management AS lm
JOIN (SELECT location_management_id, order_id 
      FROM orders 
      GROUP BY location_management_id,order_id) AS o 
  ON o.location_management_id = lm.location_management_id
JOIN (SELECT order_id, product_id, IFNULL(SUM(products.piece_qty), 0) AS inv_shipped 
      FROM orders_products 
      GROUP BY order_id, product_id) AS p 
  ON p.order_id = o.order_id
WHERE
    lm.season_id = 12 AND lm.location_id = 35;
  1. 如果您的别名与列名相同，则无需设置别名；例如lm.season_id AS season_id。如果您删除.. AS season_id，该列仍将被识别season_id。您不会将其视为lm.season_id.. 至少对于我所知道的大多数工具而言。另外，我个人认为别名是为了缩短长表或列名，但“每个人自己”。
  2. GROUP BY应包括SELECT. 当然，如果sql_mode=only_full_group_by关闭，您可以运行查询，但正确的设置应该是 ON。你可以在这里阅读更多的原因。
  3. 使用中添加的其他列GROUP BY，此查询可能不会返回您曾经拥有的结果。这取决于您的数据，如果发生这种情况，我建议您编辑您的问题并添加一个Minimal, reproducible example。目前，我们只看到查询，没有可使用的示例表/数据。如果您可以用几行数据创建一个小提琴，那就更好了。

推荐阅读