mysql查询3个表，join或union哪一个最好计算余额？
通过以下3个表，如何以最佳/更快的方式计算百万条记录的库存余额？！
根据我的经验，使用 join 可以花费不到 0.1 秒的时间，但不是最终结果，
使用 union 可以获得最终结果，但需要 1 秒...
stocktable stid productid qty 1 1 100 2 2 200 sellstable sellid stid qty 1 1 50 2 1 30 scraptable scrapid stid qty 1 1 10 2 1 5 3 2 100
SELECT
a.stid,
qty,
sellid,
b.qty,
scrapid,
c.qty
FROM
stocktable AS a
LEFT JOIN sellstable AS b ON a.stid = b.stid
LEFT JOIN scraptable AS c ON a.stid = c.stid
1 100 1 50 1 10 1 100 1 50 1 5 1 100 2 30 1 10 1 100 2 30 1 5 2 200 null null 3 100
使用 join 进行内部查询时速度很快，但如何总结余额...
添加信息：
the result should be 1 5 2 100
谢谢！
With the following 3 tables, how to calculate the stock balance for million records in a best/faster way ?!
As my experience, using join can take less than 0.1 sec but not the final result,
using union can got the final result but it takes 1x sec...
stocktable stid productid qty 1 1 100 2 2 200 sellstable sellid stid qty 1 1 50 2 1 30 scraptable scrapid stid qty 1 1 10 2 1 5 3 2 100
SELECT
a.stid,
qty,
sellid,
b.qty,
scrapid,
c.qty
FROM
stocktable AS a
LEFT JOIN sellstable AS b ON a.stid = b.stid
LEFT JOIN scraptable AS c ON a.stid = c.stid
1 100 1 50 1 10 1 100 1 50 1 5 1 100 2 30 1 10 1 100 2 30 1 5 2 200 null null 3 100
this is fast when using join for the inner query, but how to sum up the balance...
added info:
the result should be 1 5 2 100
thanks!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
如果您确实需要查询中的所有列以及三个 qty 字段的总和，那么您可以执行 SUM(a.qty+b.qty+c.qty)，但这很难准确地确定您正在寻找什么。这些表中是否有您想要分组的特定键？您是否需要 sellid 和 scrapid？
If you literally need all the columns in your query, along with the sum of the three qty fields, then you can do
SUM(a.qty+b.qty+c.qty), but it's hard to figure exactly what you are looking for. Is there a particular key in those tables that you want to group on? Do you need sellid and scrapid at all?