MySQL外键关联JOIN查询上级合同下属数量及最新日期结果错误问题

你统计结果错误的核心原因是上级合同关联Dates表时产生了行复制：

• 1个上级合同如果在Dates表中存在N条日期记录，和下属合同关联后，原本的每1条下属合同行都会被复制为N行
• 直接统计count(sub.id_contract)会统计到被复制后的行数，而非真实的下属合同数量，因此结果会比实际值偏大

你之前尝试在GROUP BY中增加date字段分组也不符合预期，是因为该操作会把同一个上级合同按不同日期拆分为多条独立记录，既会重复展示同一上级合同，计数逻辑也没有得到修正。

推荐优先使用第一种方案，逻辑更清晰，性能也更稳定。

方案1：先聚合Dates表取最新日期再关联 #

先单独聚合出每个合同对应的最新日期，保证1个合同只对应1行记录，再和你原本正确的下属合同统计逻辑关联，不会对原有计数造成干扰：

SELECT 
    COUNT(sub.id_contract) AS count_sub_contracts,
    sup.id_contract AS parent_contract, 
    sup.contractor_name AS parent_contract_name,
    d.latest_date
FROM Contracts sub 
JOIN Contracts sup ON sub.parent_id = sup.id_contract
-- 子查询提前聚合每个合同的最新日期
JOIN (
    SELECT 
        id_contract,
        MAX(DATE_FORMAT(date, "%Y-%m-%d")) AS latest_date
    FROM Dates
    GROUP BY id_contract
) d ON sup.id_contract = d.id_contract
-- 所有非聚合字段都放入GROUP BY，兼容不同数据库的SQL模式要求
GROUP BY sup.id_contract, sup.contractor_name, d.latest_date;

方案2：统计下属合同时增加去重逻辑 #

如果不想新增子查询，也可以在统计下属合同数量时对合同ID去重，抵消行复制带来的计数偏差：

SELECT 
    COUNT(DISTINCT sub.id_contract) AS count_sub_contracts,
    sup.id_contract AS parent_contract, 
    sup.contractor_name AS parent_contract_name, 
    MAX(DATE_FORMAT(d.date, "%Y-%m-%d")) AS date 
FROM Contracts sub 
JOIN Contracts sup ON sub.parent_id = sup.id_contract
JOIN Dates d ON sup.id_contract = d.id_contract 
GROUP BY sup.id_contract, sup.contractor_name;

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