caojia
76 lines
3.4 KiB
SQL
76 lines
3.4 KiB
SQL
-- MySQL 5.7
|
|
-- 客户业务员存量迁移前分类清单（只读，不修改数据）。
|
|
-- 请先执行本文件，检查 ID/工号冲突，再执行 20261028_customer_sales_user_migration.sql。
|
|
-- 分类依据：精确匹配 sys_user.user_id；或按区分大小写的 sys_user.user_name 唯一匹配。
|
|
|
|
-- 分类汇总：VALID_USER_ID 保留；UNIQUE_USERNAME 可安全转换；其余分类写入冲突/未匹配清单。
|
|
SELECT classified.classification, COUNT(*) AS customer_count
|
|
FROM (
|
|
SELECT c.customer_id,
|
|
CASE
|
|
WHEN c.sales_user_id IS NULL OR TRIM(c.sales_user_id) = '' THEN 'EMPTY'
|
|
WHEN id_match.match_count > 0 AND name_match.match_count > 0 THEN 'ID_USERNAME_COLLISION'
|
|
WHEN id_match.match_count > 0 THEN 'VALID_USER_ID'
|
|
WHEN name_match.match_count = 1 THEN 'UNIQUE_USERNAME'
|
|
WHEN name_match.match_count > 1 THEN 'AMBIGUOUS_USERNAME'
|
|
ELSE 'NO_MATCH'
|
|
END AS classification
|
|
FROM ctr_customer c
|
|
LEFT JOIN (
|
|
SELECT CAST(user_id AS CHAR) AS lookup_value, COUNT(*) AS match_count
|
|
FROM sys_user
|
|
WHERE del_flag = '0'
|
|
GROUP BY CAST(user_id AS CHAR)
|
|
) id_match ON BINARY id_match.lookup_value = BINARY TRIM(c.sales_user_id)
|
|
LEFT JOIN (
|
|
SELECT BINARY TRIM(user_name) AS lookup_value, COUNT(*) AS match_count
|
|
FROM sys_user
|
|
WHERE del_flag = '0'
|
|
AND user_name IS NOT NULL
|
|
AND user_name != ''
|
|
GROUP BY BINARY TRIM(user_name)
|
|
) name_match ON name_match.lookup_value = BINARY TRIM(c.sales_user_id)
|
|
WHERE c.del_flag = '0'
|
|
) classified
|
|
GROUP BY classified.classification
|
|
ORDER BY classified.classification;
|
|
|
|
-- 逐条分类。冲突项会列出原值、user_id 候选和 user_name 候选，供管理员人工核实。
|
|
SELECT c.customer_id,
|
|
c.customer_code,
|
|
c.customer_name,
|
|
c.sales_user_id AS original_sales_user_id,
|
|
CASE
|
|
WHEN c.sales_user_id IS NULL OR TRIM(c.sales_user_id) = '' THEN 'EMPTY'
|
|
WHEN id_match.match_count > 0 AND name_match.match_count > 0 THEN 'ID_USERNAME_COLLISION'
|
|
WHEN id_match.match_count > 0 THEN 'VALID_USER_ID'
|
|
WHEN name_match.match_count = 1 THEN 'UNIQUE_USERNAME'
|
|
WHEN name_match.match_count > 1 THEN 'AMBIGUOUS_USERNAME'
|
|
ELSE 'NO_MATCH'
|
|
END AS classification,
|
|
COALESCE(id_match.match_count, 0) AS user_id_match_count,
|
|
COALESCE(name_match.match_count, 0) AS user_name_match_count,
|
|
id_match.candidate_user_ids AS user_id_candidates,
|
|
name_match.candidate_user_ids AS user_name_candidates
|
|
FROM ctr_customer c
|
|
LEFT JOIN (
|
|
SELECT CAST(user_id AS CHAR) AS lookup_value,
|
|
COUNT(*) AS match_count,
|
|
GROUP_CONCAT(CAST(user_id AS CHAR) ORDER BY user_id SEPARATOR ',') AS candidate_user_ids
|
|
FROM sys_user
|
|
WHERE del_flag = '0'
|
|
GROUP BY CAST(user_id AS CHAR)
|
|
) id_match ON BINARY id_match.lookup_value = BINARY TRIM(c.sales_user_id)
|
|
LEFT JOIN (
|
|
SELECT BINARY TRIM(user_name) AS lookup_value,
|
|
COUNT(*) AS match_count,
|
|
GROUP_CONCAT(CAST(user_id AS CHAR) ORDER BY user_id SEPARATOR ',') AS candidate_user_ids
|
|
FROM sys_user
|
|
WHERE del_flag = '0'
|
|
AND user_name IS NOT NULL
|
|
AND user_name != ''
|
|
GROUP BY BINARY TRIM(user_name)
|
|
) name_match ON name_match.lookup_value = BINARY TRIM(c.sales_user_id)
|
|
WHERE c.del_flag = '0'
|
|
ORDER BY classification, c.customer_id;
|