antissoft / lvqianmeiye_ERP

  -- =============================================================================
  -- 金三角「幽灵战队」+ 业绩 jsj_id · 完整执行脚本（A→B→C→验证→COMMIT）
  -- 日期：2026-08-06
  -- 适用：GTID 环境（阿里云 RDS）、测试/生产均可
  --
  -- 【一次性执行步骤】
  --   1) 拷贝生产数据到目标库后，整库执行本脚本 A 段（预览，确认影响面）
  --   2) 执行 B 段（备份；若 bak_20260806_* 已存在会先 DROP）
  --   3) 执行 C 段（START TRANSACTION … 到 C5 验证）
  --   4) C5 三项 remain_* 必须全是 0 → 单独执行 COMMIT;
  --      有非 0 → 执行 ROLLBACK;
  --   5) 可选执行 E 段（提交后抽检）
  --
  -- 【预期 C 段 ROW_COUNT 参考（生产拷贝首次跑）】
  --   c1 ≈ 18   c2_kd ≈ 98   c2_xh ≈ 若干   c2_tk ≈ 少量   c4 ≈ 1
  --   已修复过的库再次跑应为 0（幂等）
  -- =============================================================================
  
  
  -- #############################################################################
  -- A. 只读预览
  -- #############################################################################
  
  SELECT 'A1_orphan_active_binding' AS item, COUNT(*) AS cnt
  FROM lq_jinsanjiao_user ju
  LEFT JOIN lq_ycsd_jsj j ON j.F_Id = ju.jsj_id
  WHERE ju.F_DeleteMark = 0 AND ju.status = 'ACTIVE' AND j.F_Id IS NULL;
  
  SELECT ju.F_Id, ju.user_id, ju.user_name, ju.jsj_id, ju.F_Month, ju.is_leader, ju.F_CreatorTime
  FROM lq_jinsanjiao_user ju
  LEFT JOIN lq_ycsd_jsj j ON j.F_Id = ju.jsj_id
  WHERE ju.F_DeleteMark = 0 AND ju.status = 'ACTIVE' AND j.F_Id IS NULL
  ORDER BY ju.F_Month DESC, ju.user_name
  LIMIT 200;
  
  SELECT 'A2_orphan_kd' AS item, COUNT(*) AS cnt,
         COALESCE(SUM(CAST(k.jksyj AS DECIMAL(18,2))), 0) AS amt
  FROM lq_kd_jksyj k
  LEFT JOIN lq_ycsd_jsj j ON j.F_Id = k.jsj_id
  WHERE k.F_IsEffective = 1 AND k.jsj_id IS NOT NULL AND k.jsj_id <> '' AND j.F_Id IS NULL;
  
  SELECT 'A3_orphan_xh' AS item, COUNT(*) AS cnt, COALESCE(SUM(x.jksyj), 0) AS amt
  FROM lq_xh_jksyj x
  LEFT JOIN lq_ycsd_jsj j ON j.F_Id = x.F_jsjid
  WHERE x.F_IsEffective = 1 AND x.F_jsjid IS NOT NULL AND x.F_jsjid <> '' AND j.F_Id IS NULL;
  
  SELECT 'A4_orphan_tk' AS item, COUNT(*) AS cnt, COALESCE(SUM(h.jksyj), 0) AS amt
  FROM lq_hytk_jksyj h
  LEFT JOIN lq_ycsd_jsj j ON j.F_Id = h.F_jsjid
  WHERE (h.F_IsEffective = 1 OR h.F_IsEffective IS NULL)
    AND h.F_jsjid IS NOT NULL AND h.F_jsjid <> '' AND j.F_Id IS NULL;
  
  SELECT 'A5_multi_active' AS item, COUNT(*) AS cnt
  FROM (
    SELECT ju.user_id, ju.F_Month
    FROM lq_jinsanjiao_user ju
    INNER JOIN lq_ycsd_jsj j ON j.F_Id = ju.jsj_id AND j.yf = ju.F_Month
    WHERE ju.F_DeleteMark = 0 AND ju.status = 'ACTIVE'
    GROUP BY ju.user_id, ju.F_Month
    HAVING COUNT(*) > 1
  ) t;
  
  SELECT 'A6_migratable_orphan_kd' AS item, COUNT(*) AS cnt,
         COALESCE(SUM(CAST(k.jksyj AS DECIMAL(18,2))), 0) AS amt
  FROM lq_kd_jksyj k
  LEFT JOIN lq_ycsd_jsj j_old ON j_old.F_Id = k.jsj_id
  INNER JOIN (
    SELECT x.user_id, x.F_Month,
           SUBSTRING_INDEX(GROUP_CONCAT(x.jsj_id ORDER BY x.F_CreatorTime DESC, x.F_Id DESC), ',', 1) AS jsj_id
    FROM (
      SELECT ju.user_id, ju.F_Month, ju.jsj_id, ju.F_Id, ju.F_CreatorTime
      FROM lq_jinsanjiao_user ju
      INNER JOIN lq_ycsd_jsj j ON j.F_Id = ju.jsj_id AND j.yf = ju.F_Month
      WHERE ju.F_DeleteMark = 0 AND ju.status = 'ACTIVE'
    ) x
    GROUP BY x.user_id, x.F_Month
  ) v ON v.user_id = COALESCE(NULLIF(k.jks, ''), NULLIF(k.jkszh, ''))
     AND v.F_Month = DATE_FORMAT(k.yjsj, '%Y%m')
  WHERE k.F_IsEffective = 1
    AND k.jsj_id IS NOT NULL AND k.jsj_id <> ''
    AND j_old.F_Id IS NULL
    AND v.jsj_id <> k.jsj_id;
  
  
  -- #############################################################################
  -- B. 事务外备份（GTID：CREATE LIKE + INSERT）
  -- #############################################################################
  
  DROP TABLE IF EXISTS bak_20260806_jinsanjiao_user_orphan;
  DROP TABLE IF EXISTS bak_20260806_kd_jksyj_orphan;
  DROP TABLE IF EXISTS bak_20260806_xh_jksyj_orphan;
  DROP TABLE IF EXISTS bak_20260806_hytk_jksyj_orphan;
  
  CREATE TABLE bak_20260806_jinsanjiao_user_orphan LIKE lq_jinsanjiao_user;
  INSERT INTO bak_20260806_jinsanjiao_user_orphan
  SELECT ju.*
  FROM lq_jinsanjiao_user ju
  LEFT JOIN lq_ycsd_jsj j ON j.F_Id = ju.jsj_id
  WHERE ju.F_DeleteMark = 0 AND ju.status = 'ACTIVE' AND j.F_Id IS NULL;
  
  CREATE TABLE bak_20260806_kd_jksyj_orphan LIKE lq_kd_jksyj;
  INSERT INTO bak_20260806_kd_jksyj_orphan
  SELECT k.*
  FROM lq_kd_jksyj k
  LEFT JOIN lq_ycsd_jsj j ON j.F_Id = k.jsj_id
  WHERE k.F_IsEffective = 1 AND k.jsj_id IS NOT NULL AND k.jsj_id <> '' AND j.F_Id IS NULL;
  
  CREATE TABLE bak_20260806_xh_jksyj_orphan LIKE lq_xh_jksyj;
  INSERT INTO bak_20260806_xh_jksyj_orphan
  SELECT x.*
  FROM lq_xh_jksyj x
  LEFT JOIN lq_ycsd_jsj j ON j.F_Id = x.F_jsjid
  WHERE x.F_IsEffective = 1 AND x.F_jsjid IS NOT NULL AND x.F_jsjid <> '' AND j.F_Id IS NULL;
  
  CREATE TABLE bak_20260806_hytk_jksyj_orphan LIKE lq_hytk_jksyj;
  INSERT INTO bak_20260806_hytk_jksyj_orphan
  SELECT h.*
  FROM lq_hytk_jksyj h
  LEFT JOIN lq_ycsd_jsj j ON j.F_Id = h.F_jsjid
  WHERE (h.F_IsEffective = 1 OR h.F_IsEffective IS NULL)
    AND h.F_jsjid IS NOT NULL AND h.F_jsjid <> '' AND j.F_Id IS NULL;
  
  SELECT 'bak_user' AS t, COUNT(*) AS cnt FROM bak_20260806_jinsanjiao_user_orphan
  UNION ALL SELECT 'bak_kd', COUNT(*) FROM bak_20260806_kd_jksyj_orphan
  UNION ALL SELECT 'bak_xh', COUNT(*) FROM bak_20260806_xh_jksyj_orphan
  UNION ALL SELECT 'bak_tk', COUNT(*) FROM bak_20260806_hytk_jksyj_orphan;
  
  
  -- #############################################################################
  -- C. 事务内修复
  -- #############################################################################
  
  START TRANSACTION;
  
  SET SESSION group_concat_max_len = 102400;
  
  DROP TEMPORARY TABLE IF EXISTS tmp_valid_jsj_binding;
  CREATE TEMPORARY TABLE tmp_valid_jsj_binding AS
  SELECT
    x.user_id,
    x.F_Month,
    SUBSTRING_INDEX(GROUP_CONCAT(x.jsj_id ORDER BY x.F_CreatorTime DESC, x.F_Id DESC), ',', 1) AS jsj_id
  FROM (
    SELECT ju.user_id, ju.F_Month, ju.jsj_id, ju.F_Id, ju.F_CreatorTime
    FROM lq_jinsanjiao_user ju
    INNER JOIN lq_ycsd_jsj j ON j.F_Id = ju.jsj_id AND j.yf = ju.F_Month
    WHERE ju.F_DeleteMark = 0 AND ju.status = 'ACTIVE'
  ) x
  GROUP BY x.user_id, x.F_Month;
  
  -- C1 软删幽灵 ACTIVE 绑定
  UPDATE lq_jinsanjiao_user ju
  LEFT JOIN lq_ycsd_jsj j ON j.F_Id = ju.jsj_id
  SET ju.F_DeleteMark = 1,
      ju.status = 'INACTIVE',
      ju.F_LastModifyTime = NOW(),
      ju.F_LastModifyUserId = 'prod-fix-20260806-orphan-binding'
  WHERE ju.F_DeleteMark = 0
    AND ju.status = 'ACTIVE'
    AND j.F_Id IS NULL;
  
  SELECT ROW_COUNT() AS c1_orphan_binding_updated;
  
  -- C2 迁移幽灵业绩 → 有效战队
  UPDATE lq_kd_jksyj k
  LEFT JOIN lq_ycsd_jsj j_old ON j_old.F_Id = k.jsj_id
  INNER JOIN tmp_valid_jsj_binding v
    ON v.user_id = COALESCE(NULLIF(k.jks, ''), NULLIF(k.jkszh, ''))
   AND v.F_Month = DATE_FORMAT(k.yjsj, '%Y%m')
  SET k.jsj_id = v.jsj_id
  WHERE k.F_IsEffective = 1
    AND k.jsj_id IS NOT NULL AND k.jsj_id <> ''
    AND j_old.F_Id IS NULL
    AND v.jsj_id <> k.jsj_id;
  
  SELECT ROW_COUNT() AS c2_kd_updated;
  
  UPDATE lq_xh_jksyj x
  LEFT JOIN lq_ycsd_jsj j_old ON j_old.F_Id = x.F_jsjid
  INNER JOIN tmp_valid_jsj_binding v
    ON v.user_id = COALESCE(NULLIF(x.jks, ''), NULLIF(x.jkszh, ''))
   AND v.F_Month = DATE_FORMAT(x.yjsj, '%Y%m')
  SET x.F_jsjid = v.jsj_id
  WHERE x.F_IsEffective = 1
    AND x.F_jsjid IS NOT NULL AND x.F_jsjid <> ''
    AND j_old.F_Id IS NULL
    AND v.jsj_id <> x.F_jsjid;
  
  SELECT ROW_COUNT() AS c2_xh_updated;
  
  UPDATE lq_hytk_jksyj h
  LEFT JOIN lq_ycsd_jsj j_old ON j_old.F_Id = h.F_jsjid
  INNER JOIN tmp_valid_jsj_binding v
    ON v.user_id = COALESCE(NULLIF(h.jks, ''), NULLIF(h.jkszh, ''))
   AND v.F_Month = DATE_FORMAT(h.tksj, '%Y%m')
  SET h.F_jsjid = v.jsj_id
  WHERE (h.F_IsEffective = 1 OR h.F_IsEffective IS NULL)
    AND h.F_jsjid IS NOT NULL AND h.F_jsjid <> ''
    AND j_old.F_Id IS NULL
    AND v.jsj_id <> h.F_jsjid;
  
  SELECT ROW_COUNT() AS c2_tk_updated;
  
  -- C4 同人同月多 ACTIVE：只留最新
  DROP TEMPORARY TABLE IF EXISTS tmp_keep_binding;
  CREATE TEMPORARY TABLE tmp_keep_binding AS
  SELECT ju.user_id, ju.F_Month,
         SUBSTRING_INDEX(GROUP_CONCAT(ju.F_Id ORDER BY ju.F_CreatorTime DESC, ju.F_Id DESC), ',', 1) AS keep_id
  FROM lq_jinsanjiao_user ju
  INNER JOIN lq_ycsd_jsj j ON j.F_Id = ju.jsj_id AND j.yf = ju.F_Month
  WHERE ju.F_DeleteMark = 0 AND ju.status = 'ACTIVE'
  GROUP BY ju.user_id, ju.F_Month
  HAVING COUNT(*) > 1;
  
  DROP TEMPORARY TABLE IF EXISTS tmp_close_binding_ids;
  CREATE TEMPORARY TABLE tmp_close_binding_ids AS
  SELECT ju.F_Id
  FROM lq_jinsanjiao_user ju
  INNER JOIN tmp_keep_binding k
    ON k.user_id = ju.user_id AND k.F_Month = ju.F_Month
  INNER JOIN lq_ycsd_jsj j ON j.F_Id = ju.jsj_id AND j.yf = ju.F_Month
  WHERE ju.F_DeleteMark = 0
    AND ju.status = 'ACTIVE'
    AND ju.F_Id <> k.keep_id;
  
  UPDATE lq_jinsanjiao_user ju
  INNER JOIN tmp_close_binding_ids c ON c.F_Id = ju.F_Id
  SET ju.F_DeleteMark = 1,
      ju.status = 'INACTIVE',
      ju.F_LastModifyTime = NOW(),
      ju.F_LastModifyUserId = 'prod-fix-20260806-multi-active';
  
  SELECT ROW_COUNT() AS c4_multi_active_closed;
  
  -- C5 验证（三项 cnt 必须全是 0）
  SELECT 'remain_orphan_active_binding' AS check_item, COUNT(*) AS cnt
  FROM lq_jinsanjiao_user ju
  LEFT JOIN lq_ycsd_jsj j ON j.F_Id = ju.jsj_id
  WHERE ju.F_DeleteMark = 0 AND ju.status = 'ACTIVE' AND j.F_Id IS NULL
  UNION ALL
  SELECT 'remain_migratable_orphan_kd', COUNT(*)
  FROM lq_kd_jksyj k
  LEFT JOIN lq_ycsd_jsj j_old ON j_old.F_Id = k.jsj_id
  INNER JOIN tmp_valid_jsj_binding v
    ON v.user_id = COALESCE(NULLIF(k.jks, ''), NULLIF(k.jkszh, ''))
   AND v.F_Month = DATE_FORMAT(k.yjsj, '%Y%m')
  WHERE k.F_IsEffective = 1 AND k.jsj_id <> '' AND j_old.F_Id IS NULL AND v.jsj_id <> k.jsj_id
  UNION ALL
  SELECT 'remain_multi_active', COUNT(*)
  FROM (
    SELECT ju.user_id, ju.F_Month
    FROM lq_jinsanjiao_user ju
    INNER JOIN lq_ycsd_jsj j ON j.F_Id = ju.jsj_id AND j.yf = ju.F_Month
    WHERE ju.F_DeleteMark = 0 AND ju.status = 'ACTIVE'
    GROUP BY ju.user_id, ju.F_Month
    HAVING COUNT(*) > 1
  ) t;
  
  SELECT COUNT(*) AS orphan_kd_no_valid_binding
  FROM lq_kd_jksyj k
  LEFT JOIN lq_ycsd_jsj j ON j.F_Id = k.jsj_id
  LEFT JOIN tmp_valid_jsj_binding v
    ON v.user_id = COALESCE(NULLIF(k.jks, ''), NULLIF(k.jkszh, ''))
   AND v.F_Month = DATE_FORMAT(k.yjsj, '%Y%m')
  WHERE k.F_IsEffective = 1
    AND k.jsj_id IS NOT NULL AND k.jsj_id <> ''
    AND j.F_Id IS NULL
    AND v.jsj_id IS NULL;
  
  
  -- #############################################################################
  -- D. 提交（C5 三项全是 0 后，单独执行本行）
  -- #############################################################################
  -- COMMIT;
  -- ROLLBACK;
  
  
  -- #############################################################################
  -- E. 提交后抽检（可选）
  -- #############################################################################
  
  -- 郑美函 8 月业绩应只在燃爆队 853054127826011397
  -- SELECT 'kd' src, jsj_id, COUNT(*) cnt, SUM(CAST(jksyj AS DECIMAL(18,2))) amt
  -- FROM lq_kd_jksyj
  -- WHERE F_IsEffective=1 AND yjsj>='2026-08-01' AND yjsj<'2026-09-01'
  --   AND (jks='13989177235' OR jkszh='13989177235')
  -- GROUP BY jsj_id;