mysql - 带有连接和选择的mysql查询结果有问题

Question

你好，

我正在尝试为我的库存报告加入 3 个表，但我从中得到了奇怪的结果。

我的问题

SELECT i_inventory.xid,
       count(x_transaction_details.xitem) AS occurrence,
       i_inventory.xitem AS itemName,
       SUM(i_items_group.or_qty) AS `openingQty`,
       avg(x_transaction_details.cost) AS avg_cost,
       SUM(x_transaction_details.qty) AS totalNumberSold,
       SUM(i_items_group.or_qty) - SUM(x_transaction_details.qty) AS totalRemQty
FROM x_transaction_details
LEFT JOIN i_inventory ON x_transaction_details.xitem = i_inventory.xid
LEFT JOIN i_items_group ON i_inventory.xid = i_items_group.xitem
WHERE (x_transaction_details.date_at BETWEEN '2015-01-18 03:14:54' AND '2015-10-18 03:14:54')
  AND i_inventory.xid = 3840
GROUP BY x_transaction_details.xitem
ORDER BY occurrence DESC

这个查询给了我这个结果:

看到openingQty列，然后我尝试做一个简单的查询来验证结果，

这是我的检查 openingQty 的查询，仅连接 2 个表 i_items_group 表(存储批处理)和 i_inventory 表(存储项目信息)。

SELECT i_inventory.xid,
       i_inventory.xitem,
       SUM(i_items_group.or_qty) AS openingQty,
       i_items_group.cost
FROM i_inventory
INNER JOIN i_items_group ON i_inventory.xid = i_items_group.xitem
WHERE i_inventory.xid = 3840
  AND (i_items_group.date_at BETWEEN '2015-01-18 03:14:54' AND '2015-10-18 03:14:54')

我的结果是:

这是正确的数据。

我还对我的 x_transaction_details 表进行了查询，以验证其是否正确。

这是我的查询:

select xitem, qty as qtySold from x_transaction_details where xitem = 3840 
AND (date_at BETWEEN '2015-01-18 03:14:54' AND '2015-10-18 03:14:54')

结果:

总计:15-quatitySold。

我只是对我如何得到 3269 作为查询结果感到困惑，因为真正的 openingQty 应该只有 467。

我想问题出在我的连接查询中，它弄乱了交易数量然后将其总结(虽然我真的不知道)。

能否请您帮我识别它，并帮我提出正确的查询。

Answer 1

这是单个查询中多个 SUM 语句的常见问题。请记住 SQL 如何进行聚合:首先它生成一组未聚合的数据，然后聚合它。在不使用 GROUP BY 或聚合函数的情况下尝试您的查询，您会对结果感到惊讶。你的帖子中没有足够的正确细节让我确定故障在哪里，但我可以猜到。

看起来您有一个对应于某种产品的 xitem，然后您已将其加入交易和项目组。假设一个特定的 xitem 与 3 个交易和 5 个项目组匹配。您将从该联接中获得 15 条记录。当您对其求和时，基于交易表中的字段的任何 SUM 计算将比您预期的高 5 倍，而来自项目组表的任何 SUM 计算将比您预期的高 3 倍。这里的关键症状是聚合结果是正确值的倍数，但不同行的倍数似乎不同。

有多种方法可以解决此类错误。一些开发人员喜欢在子查询中计算其中一个聚合，然后在主查询中进行另一个聚合，并根据子查询中已经正确的结果进行分组。其他人喜欢编写内联查询以在表达式中进行聚合:

SELECT xitem, (SELECT SUM(i_items_group.or_qty) FROM i_items_group WHERE i_inventory.xid = i_items_group.xitem) AS `openingQty`
, -- select more fields

找出最适合您的方法。但是，如果您想亲自查看证据，请在没有聚合的情况下运行此查询，您将看到为什么这些 SUM 正在做它们正在做的事情:

SELECT i_inventory.xid,
       x_transaction_details.xitem AS occurrence,
       i_inventory.xitem AS itemName,
       i_items_group.or_qty,
       x_transaction_details.cost,
       x_transaction_details.qty,
       i_items_group.or_qty - x_transaction_details.qty AS RemainingQty
FROM x_transaction_details
LEFT JOIN i_inventory ON x_transaction_details.xitem = i_inventory.xid
LEFT JOIN i_items_group ON i_inventory.xid = i_items_group.xitem
WHERE (x_transaction_details.date_at BETWEEN '2015-01-18 03:14:54' AND '2015-10-18 03:14:54')
  AND i_inventory.xid = 3840

ORDER BY occurrence DESC