MySQL查询：按客户经理汇总客户最新合同的Units总数

我有两张表：

• 客户表（customers）：存储客户数据，包含分配的客户经理Manager2字段
• 合同表（contracts）：存储对应客户的合同数据，客户可能有多条历史合同，最新记录为当前生效的合同/补充协议，Number_of_Units字段值随合同变化，生效日期无统一规律

需求：编写MySQL查询，按客户经理汇总其负责客户的最新合同中的Number_of_Units总数。目前能提取所有最新合同、汇总所有合同的Units总数，但无法实现按特定客户经理汇总其客户的最新合同Units总数。

现有查询语句 #

select *
from (
SELECT contracts.*, customers.Manager2
FROM contracts
LEFT JOIN customers ON contracts.CID = customers.CID
WHERE contracts.Status = Active AND contracts.Effective_Date <= NOW() AND customers.Manager2 = 'person2'
ORDER BY contracts.Contract_ID DESC
) x 
group by CID

表数据 #

客户表（customers） #

合同表（contracts） #

期望查询结果 #

person1 85
person2 1887
person3 700

解决方案 #

方法1：使用窗口函数（MySQL 8.0+） #

通过ROW_NUMBER()标记每个客户的最新合同，再筛选后按客户经理汇总：

SELECT 
    c.Manager2,
    SUM(ct.Number_of_Units) AS Total_Units
FROM (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY CID ORDER BY Contract_ID DESC) AS rn
    FROM contracts
    WHERE Status = 'Active' AND Effective_Date <= NOW()
) ct
JOIN customers c ON ct.CID = c.CID
WHERE ct.rn = 1
GROUP BY c.Manager2
ORDER BY c.Manager2;

方法2：关联子查询（兼容低版本MySQL） #

先获取每个客户的最新合同ID，再关联求和：

SELECT 
    c.Manager2,
    SUM(ct.Number_of_Units) AS Total_Units
FROM contracts ct
JOIN customers c ON ct.CID = c.CID
JOIN (
    SELECT CID, MAX(Contract_ID) AS Max_Contract_ID
    FROM contracts
    WHERE Status = 'Active' AND Effective_Date <= NOW()
    GROUP BY CID
) latest ON ct.CID = latest.CID AND ct.Contract_ID = latest.Max_Contract_ID
WHERE c.Status = 'Active'
GROUP BY c.Manager2
ORDER BY c.Manager2;

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