MySQL JOIN与GROUP BY查询：按公司汇总指定状态最新记录

需求梳理 #

• 关联SERVICE与LOG两张表
• 仅保留Status为diagnosis或done的记录
• 按Company分组汇总，每个(cod, Company)组合仅保留最新日期的条目
• SERVICE表中同一(cod, Company)对应唯一client，需先做去重处理

分步实现SQL #

1. 获取每个(cod, Company)的最新有效日期

先从LOG表筛选符合状态要求的记录，分组后提取每组的最大日期：

SELECT cod, Company, MAX(date) AS last_date
FROM LOG
WHERE Status IN ('diagnosis', 'done')
GROUP BY cod, Company

2. 匹配回LOG表拿到对应状态

用上述子查询的结果关联LOG表，定位到每个组合的最新状态记录：

SELECT l.cod, l.Company, l.Status AS `Last Status`, l.date AS `Last date`
FROM LOG l
JOIN (
    SELECT cod, Company, MAX(date) AS last_date
    FROM LOG
    WHERE Status IN ('diagnosis', 'done')
    GROUP BY cod, Company
) latest ON l.cod = latest.cod AND l.Company = latest.Company AND l.date = latest.last_date
WHERE l.Status IN ('diagnosis', 'done')

3. 关联去重后的SERVICE表

由于SERVICE表同一(cod, Company)存在多条重复行，先对其去重，再关联上述状态结果：

SELECT 
    s.cod, 
    s.Company, 
    s.client, 
    log_latest.`Last Status`, 
    log_latest.`Last date`
FROM (
    -- 对SERVICE表去重，保留每个(cod, Company)对应的唯一client
    SELECT DISTINCT cod, Company, client
    FROM SERVICE
) s
JOIN (
    -- 获取每个(cod, Company)的最新有效状态记录
    SELECT l.cod, l.Company, l.Status AS `Last Status`, l.date AS `Last date`
    FROM LOG l
    JOIN (
        SELECT cod, Company, MAX(date) AS last_date
        FROM LOG
        WHERE Status IN ('diagnosis', 'done')
        GROUP BY cod, Company
    ) latest ON l.cod = latest.cod AND l.Company = latest.Company AND l.date = latest.last_date
    WHERE l.Status IN ('diagnosis', 'done')
) log_latest ON s.cod = log_latest.cod AND s.Company = log_latest.Company
ORDER BY s.Company, s.cod;

预期结果验证 #

执行上述SQL后，会得到与需求一致的汇总结果：

公司A汇总

公司B汇总

关键说明 #

• DISTINCT用于处理SERVICE表的重复行，确保每个(cod, Company)仅返回一个client
• 子查询latest的作用是锁定每组的最新日期，避免直接GROUP BY导致状态值匹配错误
• 两次使用WHERE Status IN ('diagnosis', 'done')，分别用于提前筛选有效记录、最终匹配正确状态

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