如何用JOIN和GROUP BY编写SQL查询指定零件近5次工单的平均生产耗时

前提说明 #

你原有的SQL已经实现了单工单维度的聚合计算，但存在两个待优化点：

• 缺少排序逻辑，TOP 5返回的结果不固定，无法保证是最近的5个工单，需要补充按工单时间倒序的逻辑（假设JobHeader表存在CreateTime字段存储工单创建时间，若你实际存储工单时间的字段名不同可替换）
• 缺少外层的平均值聚合逻辑，需要将现有查询作为子查询后做平均计算

修改后SQL #

SELECT 
    RegNo,
    AVG(BOMQty) AS AvgBOMQty,
    AVG(TotalTime) AS AvgTotalTime
FROM (
    SELECT TOP 5 
        JobHeader.RegNo, 
        JobHeader.BOMQty, 
        SUM(JobLabour.WorkedTime) AS TotalTime 
    FROM JobHeader 
    INNER JOIN JobLabour ON JobHeader.JobNo = JobLabour.JobNo
    WHERE JobHeader.RegNo = 'RM-BRU-0134' 
    GROUP BY JobHeader.BOMQty, JobHeader.JobNo, JobHeader.RegNo
    -- 补充排序确保取到最近5个工单，无CreateTime可按自增工单号JobHeader.JobNo DESC排序，字段可根据你实际表结构调整
    ORDER BY JobHeader.CreateTime DESC
) AS Recent5Jobs
GROUP BY RegNo

逻辑说明 #

• 内层子查询和你原有逻辑一致，补充排序后稳定返回指定零件最近5个工单的单工单计划量、总耗时数据
• 外层查询对内层返回的5条数据做聚合，分别计算BOMQty的平均值、总耗时的平均值

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