Umar Asked: 2023-09-18 20:21:37 +0800 CST2023-09-18 20:21:37 +0800 CST 2023-09-18 20:21:37 +0800 CST 如何加快查询库存余额? 772 我有 2 个表StockIn和StockOut计算的库存余额(进出)。 这两个表都包含数百万条记录。 当我计算库存余额时,由于有数百万条记录,我的查询崩溃了。 有人可以帮助我加快或修复此查询吗? SELECT Main.Item, SUM(Main.Qty) AS Qty FROM (SELECT item, Qty FROM StockIn UNION ALL SELECT item, -Qty FROM Stockout) AS Main GROUP BY Main.Item sql-server 1 个回答 Voted Best Answer Andrea B. 2023-09-18T20:49:58+08:002023-09-18T20:49:58+08:00 分别计算所有入库和出库的小计,然后将它们相加得到余额。 SELECT Main.Item, SUM(Main.Qty) AS Qty FROM (SELECT item, SUM(Qty) as Qty FROM StockIn GROUP BY item UNION ALL SELECT item, -SUM(Qty) as Qty FROM Stockout GROUP BY item) AS Main GROUP BY Main.Item 还要确保两个表都在该item列上建立了索引
分别计算所有入库和出库的小计,然后将它们相加得到余额。
还要确保两个表都在该
item
列上建立了索引