MS SQL多表关联查询SUM()聚合仅统计最新记录工时的问题

问题描述 #

需要统计每台设备对应未完成工单的预估总工时，需关联change、part、completed三张表获取最新变更记录的工时值。目前已能查询到待求和的工时列表，但尝试使用SUM()函数进行汇总时始终抛出聚合错误，原有SQL代码如下：

SELECT 
(SELECT TOP 1 change.hours 
    FROM change WHERE change.id = part.id
    ORDER BY change.timeStamp DESC) as 'Hours'

FROM change 
INNER JOIN part ON (part.id = change.id)
INNER JOIN  completed ON (part.id = completed.id) 

WHERE part.id NOT IN (SELECT completed.id FROM completed WHERE completed.completed = 1) 
and (SELECT TOP 1 change.machine FROM change WHERE part.id = change.id ORDER BY change.timeStamp DESC ) = :machine

GROUP BY change.id, part.id

预期输出 #

• 设备1总工时12小时
• 设备2总工时18小时
• 设备3总工时18小时

相关表结构与数据 #

part表 #

change表1 #

change表2 #

修正方案 #

原有SQL报错的原因是直接对嵌套子查询的结果聚合时，聚合维度与分组逻辑不匹配。可以先通过窗口函数拿到每个part id对应的最新change记录，再过滤未完成工单后按设备求和，修正后的SQL如下（兼容SQL Server语法，适配你提供的TOP 1写法）：

-- 先获取每个part id对应的最新变更工时和所属设备
WITH latest_change AS (
    SELECT 
        id,
        hours,
        machine,
        ROW_NUMBER() OVER (PARTITION BY id ORDER BY timeStamp DESC) AS rn
    FROM change
)
SELECT 
    CONCAT('设备', lc.machine, '总工时', SUM(lc.hours), '小时') AS result
FROM latest_change lc
INNER JOIN part p ON lc.id = p.id
INNER JOIN completed c ON p.id = c.id
WHERE lc.rn = 1 -- 只取最新的变更记录
AND c.completed != 1 -- 过滤未完成工单
GROUP BY lc.machine
ORDER BY lc.machine

如果需要查询单台设备的总工时，直接在WHERE条件中添加lc.machine = :machine即可，返回结果就是对应设备的单值总工时，可直接用于GUI展示。

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