报表最新版本查询中Injuries统计值错误的SQL修复请求

核心问题：原SQL因关联多版本报表数据，导致每条injuries记录被重复关联18次（对应18个版本），最终SUM和COUNT的计算结果被放大18倍，出现实际2条却统计为36条的错误。

修复后的SQL（方案一：子查询独立统计） #

SELECT 
    raports.*,
    r1.*,
    users.*, 
    (SELECT COUNT(*) FROM changes WHERE changes.changes_raports_id = raports.raports_id) as changes,
    (SELECT changes.changes_date FROM changes WHERE changes.changes_raports_id = raports.raports_id ORDER BY changes.changes_date DESC LIMIT 1) as last_change,
    -- 子查询单独计算最新版本下的min平均值
    (SELECT SUM(i.injuries_min_procent) / COUNT(itr.injuries_to_raports_id)
     FROM injuries_to_raports itr
     JOIN injuries i ON itr.injuries_to_raports_injuries_id = i.injuries_id
     WHERE itr.injuries_to_raports_raports_id = raports.raports_id
       AND EXISTS (
           SELECT 1 FROM raports_to_changes r2
           WHERE r2.raports_to_changes_raports_id = raports.raports_id
             AND r2.raports_to_changes_changes_id = (
                 SELECT MAX(raports_to_changes_changes_id) 
                 FROM raports_to_changes 
                 WHERE raports_to_changes_raports_id = raports.raports_id
             )
       )) as min,
    -- 子查询单独计算最新版本下的max平均值
    (SELECT SUM(i.injuries_max_procent) / COUNT(itr.injuries_to_raports_id)
     FROM injuries_to_raports itr
     JOIN injuries i ON itr.injuries_to_raports_injuries_id = i.injuries_id
     WHERE itr.injuries_to_raports_raports_id = raports.raports_id
       AND EXISTS (
           SELECT 1 FROM raports_to_changes r2
           WHERE r2.raports_to_changes_raports_id = raports.raports_id
             AND r2.raports_to_changes_changes_id = (
                 SELECT MAX(raports_to_changes_changes_id) 
                 FROM raports_to_changes 
                 WHERE raports_to_changes_raports_id = raports.raports_id
             )
       )) as max
FROM raports
LEFT JOIN users 
    ON users.users_id = raports.raports_users_id 
-- 只关联最新版本的报表变更记录，避免主查询产生重复行
LEFT JOIN raports_to_changes r1
    ON r1.raports_to_changes_raports_id = raports.raports_id
    AND r1.raports_to_changes_changes_id = (
        SELECT MAX(raports_to_changes_changes_id) 
        FROM raports_to_changes 
        WHERE raports_to_changes_raports_id = raports.raports_id
    )
GROUP BY raports.raports_id, r1.raports_to_changes_id, users.users_id 
ORDER BY raports.raports_id ASC;

修复后的SQL（方案二：CTE预取最新版本，逻辑更清晰） #

-- 先预计算每个报表的最新版本ID
WITH latest_raport_version AS (
    SELECT 
        raports_to_changes_raports_id,
        MAX(raports_to_changes_changes_id) as latest_change_id
    FROM raports_to_changes
    GROUP BY raports_to_changes_raports_id
)
SELECT 
    raports.*,
    r1.*,
    users.*, 
    (SELECT COUNT(*) FROM changes WHERE changes.changes_raports_id = raports.raports_id) as changes,
    (SELECT changes.changes_date FROM changes WHERE changes.changes_raports_id = raports.raports_id ORDER BY changes.changes_date DESC LIMIT 1) as last_change,
    -- 用COALESCE处理无injuries时的空值问题
    COALESCE(SUM(injuries.injuries_min_procent) / COUNT(injuries_to_raports.injuries_to_raports_id), 0) as min,
    COALESCE(SUM(injuries.injuries_max_procent) / COUNT(injuries_to_raports.injuries_to_raports_id), 0) as max
FROM raports
LEFT JOIN users 
    ON users.users_id = raports.raports_users_id 
LEFT JOIN latest_raport_version lrv
    ON lrv.raports_to_changes_raports_id = raports.raports_id
LEFT JOIN raports_to_changes r1
    ON r1.raports_to_changes_raports_id = raports.raports_id
    AND r1.raports_to_changes_changes_id = lrv.latest_change_id
LEFT JOIN injuries_to_raports 
    ON injuries_to_raports.injuries_to_raports_raports_id = raports.raports_id
LEFT JOIN injuries
    ON injuries_to_raports.injuries_to_raports_injuries_id = injuries.injuries_id
-- 只统计最新版本关联的injuries数据
WHERE EXISTS (
    SELECT 1 FROM raports_to_changes r2
    WHERE r2.raports_to_changes_raports_id = raports.raports_id
      AND r2.raports_to_changes_changes_id = lrv.latest_change_id
) OR injuries.injuries_id IS NULL
GROUP BY raports.raports_id, r1.raports_to_changes_id, users.users_id 
ORDER BY raports.raports_id ASC;

关键修改点 #

1. 避免笛卡尔积：原查询直接关联changes表导致报表行被版本数重复，现在通过子查询/CTE只取每个报表的最新版本，减少主查询重复行。
2. 独立统计injuries：将min/max计算移到子查询中，直接基于最新版本的报表关联关系筛选数据，彻底避免多表关联带来的重复计数。
3. 规范GROUP BY：补充所有非聚合字段到GROUP BY中，符合SQL标准，避免分组逻辑错误。

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