SQL子查询聚合问题：按合同获取对应最后维护日期

你当前的SQL子查询返回的是数据库中所有合同的全局最后维护日期，无法实现每行对应Nro_Contract的最后维护日期，核心问题是子查询没有和外层查询的合同ID做关联。下面提供两种修正方案：

方案一：修改关联子查询 #

直接在子查询的WHERE条件里添加与外层合同的关联，让子查询只计算当前行对应合同的最大维护日期：

---Interventions with PkState "Schedule_Visit" with the Last_Maintenance aggregation
SELECT 
    Interventions.ID AS Nro_Inter, 
    MaintenanceContracts.Num AS Nro_Contract, 
    Interventions.TentativeDate AS Schedule_Visit, 
    -- 子查询添加与外层合同的关联条件，只计算当前合同的最大维护日期
    (SELECT MAX(i.AssignmentDate) 
     FROM Interventions i
     WHERE i.PkState = 'AE4B42CF-0003-4796-89F2-2881527DFB26' 
       AND i.PkMaintenanceContract = MaintenanceContracts.Pk) AS Last_Maintenance --PkState "Maintenance Executed"
FROM Interventions
INNER JOIN MaintenanceContracts ON MaintenanceContracts.Pk = Interventions.PkMaintenanceContract
WHERE Interventions.PkState = 'AE4B42CF-0000-4796-89F2-2881527ABC26' 
  AND Interventions.PkMaintenanceContract IS NOT NULL --PkState "Schedule_Visit"
-- 原GROUP BY是多余的，因为不需要对主查询的行做聚合，保留会导致不必要的分组
ORDER BY Nro_Contract

关键修改点： #

• 子查询使用别名i区分外层的Interventions表，避免字段混淆
• 新增i.PkMaintenanceContract = MaintenanceContracts.Pk，让子查询仅统计当前行对应合同的维护记录
• 移除多余的GROUP BY：主查询不需要聚合，原分组逻辑会导致结果行被错误合并

方案二：用派生表关联（更直观） #

利用你已经成功实现的按合同聚合的查询作为派生表，和主查询关联，逻辑更清晰，避免嵌套子查询的困惑：

---Interventions with PkState "Schedule_Visit" with the Last_Maintenance aggregation
SELECT 
    Interventions.ID AS Nro_Inter, 
    mc.Num AS Nro_Contract, 
    Interventions.TentativeDate AS Schedule_Visit, 
    contract_last.Last_Maintenance
FROM Interventions
INNER JOIN MaintenanceContracts mc ON mc.Pk = Interventions.PkMaintenanceContract
-- 关联已验证的合同最后维护日期派生表
INNER JOIN (
    SELECT 
        MaintenanceContracts.Pk AS ContractPk,
        MAX(Interventions.AssignmentDate) AS Last_Maintenance
    FROM MaintenanceContracts 
    INNER JOIN Interventions ON Interventions.PkMaintenanceContract = MaintenanceContracts.Pk
    WHERE Interventions.PkState = 'AE4B42CF-0003-4796-89F2-2881527DFB26' --只统计已完成的维护
      AND Interventions.PkMaintenanceContract IS NOT NULL
    GROUP BY MaintenanceContracts.Pk
) contract_last ON contract_last.ContractPk = mc.Pk
WHERE Interventions.PkState = 'AE4B42CF-0000-4796-89F2-2881527ABC26' 
  AND Interventions.PkMaintenanceContract IS NOT NULL --PkState "Schedule_Visit"
ORDER BY Nro_Contract

优势： #

• 派生表contract_last复用了你已验证的聚合逻辑，仅调整过滤条件到已完成的维护状态
• 通过合同主键ContractPk关联，确保每行都匹配对应合同的最后维护日期
• 逻辑直观，容易理解和后续维护

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