SQL Server关联Items与ItemsLedger表实现商品维度数量汇总查询
跨表关联聚合的正确SQL实现方案
原查询的报错根因
SQL Server 执行分组查询时会做严格语法校验:SELECT 子句里所有没被聚合函数包裹的字段,必须全部加入 GROUP BY 的分组字段列表。原查询只把item.No_放进了GROUP BY,但是SELECT里还选了Description、Base Unit of Measure、Unit Price三个非聚合字段,直接运行就会报“列在GROUP BY子句中无效”的语法错。
另外左连接场景下,没有匹配到库存流水的商品,聚合计算得到的数量默认是NULL,不符合日常统计的阅读习惯。
基础可运行版本(适配中小数据量)
修正分组规则,补充空值处理,最终输出完全匹配要求的5个字段:
SELECT item.[No_] AS ItemNo, item.[Description], item.[Base Unit of Measure] AS BaseUnitOfMeasure, item.[Unit Price] AS UnitPrice, ISNULL(SUM(ledger.[Quantity]), 0) AS Quantity FROM items AS item LEFT JOIN ItemLedger AS ledger ON item.[No_] = ledger.[Item No_] GROUP BY item.[No_], item.[Description], item.[Base Unit of Measure], item.[Unit Price]
对照样例数据的输出效果完全符合预期:
- ItemNo=1的商品,流水累加值为400-20-5=375,和计算要求一致
- 没有匹配流水的ItemNo=2、ItemNo=3商品,汇总数量自动赋值为0,不会出现影响使用的NULL值
高性能优化版本(适配大流水量场景)
当库存流水表数据量达到百万级以上时,直接关联后分组会增加临时表的计算开销。可以先在流水表侧完成单商品的数量汇总,再和商品基础表关联,大幅降低执行耗时:
SELECT item.[No_] AS ItemNo, item.[Description], item.[Base Unit of Measure] AS BaseUnitOfMeasure, item.[Unit Price] AS UnitPrice, ISNULL(ledger_total.agg_quantity, 0) AS Quantity FROM items AS item LEFT JOIN ( -- 先在流水表内完成单商品的数量聚合,减少关联数据量 SELECT [Item No_], SUM([Quantity]) AS agg_quantity FROM ItemLedger GROUP BY [Item No_] ) AS ledger_total ON item.[No_] = ledger_total.[Item No_]
内容的提问来源于stack exchange,提问作者MAFSAT
相关产品推荐
相关产品推荐

