shanxia2.0/docs/sql/20260712_crm_opportunity_follow_count_backfill.sql

-- 修复商机 follow_count 与跟进记录数不一致（历史数据回填）
-- 原因：创建跟进时 recalc 发生在 flush 之前，导致冗余字段长期少 1

UPDATE crm_opportunity o
LEFT JOIN (
    SELECT opportunity_id, COUNT(*) AS cnt
    FROM crm_opportunity_follow
    WHERE del_flag = 0
    GROUP BY opportunity_id
) f ON f.opportunity_id = o.id
SET o.follow_count = IFNULL(f.cnt, 0),
    o.last_time = (
        SELECT MAX(COALESCE(f2.follow_time, f2.create_time))
        FROM crm_opportunity_follow f2
        WHERE f2.opportunity_id = o.id
          AND f2.del_flag = 0
    )
WHERE o.del_flag = 0
  AND IFNULL(o.follow_count, 0) <> IFNULL(f.cnt, 0);