健康师业绩jks字段历史脏数据修复-预览与可选执行.sql
1.79 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
-- 健康师业绩 jks 字段历史脏数据修复（可选，先预览再执行）
-- 背景：部分行 jks 存姓名、jkszh 存账号/用户ID；读侧已用 jkszh 兼容，本脚本用于回填 jks 与 jkszh 一致。
-- 执行前请先跑「预览」段，确认影响行数；建议在业务低峰、可回滚环境下执行。
-- ========== 1. 预览：各表 jks 为姓名但 jkszh 为账号的行数 ==========
SELECT 'lq_xh_jksyj' AS tbl, COUNT(*) AS cnt
FROM lq_xh_jksyj
WHERE F_IsEffective = 1
AND jkszh IS NOT NULL AND jkszh <> ''
AND jks IS NOT NULL AND jks <> ''
AND jks <> jkszh
AND jks NOT REGEXP '^[0-9]+$';
SELECT 'lq_kd_jksyj' AS tbl, COUNT(*) AS cnt
FROM lq_kd_jksyj
WHERE F_IsEffective = 1
AND jkszh IS NOT NULL AND jkszh <> ''
AND jks IS NOT NULL AND jks <> ''
AND jks <> jkszh
AND jks NOT REGEXP '^[0-9]+$';
SELECT 'lq_hytk_jksyj' AS tbl, COUNT(*) AS cnt
FROM lq_hytk_jksyj
WHERE F_IsEffective = 1
AND jkszh IS NOT NULL AND jkszh <> ''
AND jks IS NOT NULL AND jks <> ''
AND jks <> jkszh
AND jks NOT REGEXP '^[0-9]+$';
-- ========== 2. 可选执行（取消注释后手动执行，勿自动化跑） ==========
-- START TRANSACTION;
--
-- UPDATE lq_xh_jksyj
-- SET jks = jkszh
-- WHERE F_IsEffective = 1
-- AND jkszh IS NOT NULL AND jkszh <> ''
-- AND jks IS NOT NULL AND jks <> ''
-- AND jks <> jkszh
-- AND jks NOT REGEXP '^[0-9]+$';
--
-- UPDATE lq_kd_jksyj
-- SET jks = jkszh
-- WHERE F_IsEffective = 1
-- AND jkszh IS NOT NULL AND jkszh <> ''
-- AND jks IS NOT NULL AND jks <> ''
-- AND jks <> jkszh
-- AND jks NOT REGEXP '^[0-9]+$';
--
-- UPDATE lq_hytk_jksyj
-- SET jks = jkszh
-- WHERE F_IsEffective = 1
-- AND jkszh IS NOT NULL AND jkszh <> ''
-- AND jks IS NOT NULL AND jks <> ''
-- AND jks <> jkszh
-- AND jks NOT REGEXP '^[0-9]+$';
--
-- COMMIT;