考虑下面的代码片段:
SELECT
vbs.SKUID, SKUName, SUM(vbs.Quantity) AS Qty
FROM
BatchStocks AS vbs
WHERE
vbs.SKUID IN (Here i have almost 12000 SKU)
AND vbs.StoreID IN (%q)
AND vbs.StockType = %n
GROUP BY
vbs.SKUID, vbs.SKUName在where条件下,我有近12000个SKUID,执行语句需要很长时间。你能建议我如何改进这个查询吗?
发布于 2018-09-25 12:45:12
您可以使用EXISTS()
将SKU放入临时表中
和
WHERE vbs.SKUID IN (Here i have almost 12000 SKU)更改为
WHERE EXISTS
(
SELECT *
FROM #TEMP x
WHERE x.SKUID = vbs.SKUID
)发布于 2018-09-25 13:34:31
您也可以使用Inner Join, EXISTS而不是IN。请参考EXISTS查询:
SELECT
vbs.SKUID, SKUName, SUM(vbs.Quantity) AS Qty
FROM
BatchStocks AS vbs
WHERE
EXISTS ( select 1 from #TEMPTABLE t on t.SKUID = vbs.SKUID)
AND vbs.StoreID IN (%q)
AND vbs.StockType = %n
GROUP BY
vbs.SKUID, vbs.SKUName关于INNER JOIN请参考下面的查询:
SELECT
vbs.SKUID, SKUName, SUM(vbs.Quantity) AS Qty
FROM
BatchStocks AS vbs
INNER JOIN #TEMPTABLE t on t.SKUID = vbs.SKUID
WHERE vbs.StoreID IN (%q)
AND vbs.StockType = %n
GROUP BY
vbs.SKUID, vbs.SKUNamehttps://stackoverflow.com/questions/52490676
复制相似问题