T-SQL主查询GROUP BY关联子查询时报列无效错误问题求助

错误原因 #

• 报错的触发点是关联子查询中引用了主查询的ro.[Order Reference]字段：主查询的聚合粒度为年+月，该字段既不在GROUP BY分组项中，也没有被聚合函数包裹，SQL引擎无法确定分组后该字段的取值逻辑，因此抛出非法字段错误，和该字段是否出现在最终SELECT返回列表无关。
• 原有查询逻辑也不符合需求：子查询会统计单个订单全量时间范围的退货数，而按照示例要求，只有和订单处理时间同月份的退货才需要计入统计，原有写法会把订单后续月份的退货也纳入计算，结果不符合预期。

修复方案 #

调整查询逻辑，先关联订单和退货记录，再按年月维度聚合即可，修正后的T-SQL代码如下：

SELECT 
    MONTH(ro.[Date processed at hub]) AS MonthNum,
    DATENAME(mm, ro.[Date processed at hub]) AS [Month],
    YEAR(ro.[Date processed at hub]) AS [Year],
    COUNT(DISTINCT ro.[Order Reference]) AS [NoofOrders],
    COUNT(DISTINCT ro.[Order Reference]) * 5.72 AS Cost,
    COUNT(ite.[Date return created]) AS NoofItems
FROM [ZoomBI].[dbo].[ReboundOrder] ro
LEFT JOIN [ZoomBI].[dbo].[ReboundOrder] ite 
    ON ro.[Order Reference] = ite.[Order Reference]
    -- 仅统计与订单处理时间同年月的退货记录
    AND YEAR(ro.[Date processed at hub]) = YEAR(ite.[Date return created])
    AND MONTH(ro.[Date processed at hub]) = MONTH(ite.[Date return created])
GROUP BY 
    YEAR(ro.[Date processed at hub]),
    DATENAME(month, ro.[Date processed at hub]),
    MONTH(ro.[Date processed at hub])
ORDER BY
    MONTH(ro.[Date processed at hub])

逻辑说明 #

• 用LEFT JOIN关联同订单的退货记录，加年月匹配条件过滤掉非当月的退货
• 用COUNT(DISTINCT ro.[Order Reference])统计订单数，避免一个订单对应多条退货记录时订单数被重复计算
• COUNT(ite.[Date return created])会自动忽略匹配不到的NULL值，统计结果即为对应年月的退货商品总数，完全匹配示例预期输出。

内容的提问来源于stack exchange，提问作者Jakey