-- ============================================================================
|
-- 把「问卷库模板」题目的选项默认值（svy_lib_template_targetoption.default_value）
|
-- 同步到「问卷任务模板」题目选项（svy_task_template_targetoption.default_value）
|
--
|
-- 表名说明（问题选项的默认值其实不在 script 表里，而在 option 表里）：
|
-- 库 题目 svy_lib_template_script ── id ← scriptid ── 库 选项 svy_lib_template_targetoption
|
-- 任务 题目 svy_task_template_script ── id ← scriptid ── 任务 选项 svy_task_template_targetoption
|
-- default_value 语义：1=否（默认值），2=是（默认选项），NULL=未设置
|
--
|
-- 任务模板 → 库模板 的映射链（实测 smartor_nhfy）：
|
-- svy_task_template_script.templateid
|
-- = svy_task_template.id
|
-- → svy_task_template.templateid = svy_lib_template.svyid
|
-- → svy_lib_template_script.svyid，题面 script_content 相同即同一道题
|
-- → svy_lib_template_targetoption.scriptid = svy_lib_template_script.id
|
-- 且 optioncontent 相同即同一个选项
|
--
|
-- 实测覆盖（2026-11-06，smartor_nhfy）：
|
-- 任务模板 710 个，能映射到库模板的 663 个（另外 47 个模板 / 879 个选项没有库来源，
|
-- 本脚本不会动它们，需要人工处理）；
|
-- 任务题目 6409 道，按题面能对上库题目 6353 道（99.1%）；
|
-- 任务选项 16405 个，能对上库选项 16369 个（99.8%）。
|
--
|
-- 三个方案，按需要只跑其中一个：
|
-- 方案A（推荐）：只把 lib 里标了默认(=2)的选项推到 task，不动 task 已有的其它值
|
-- → 实测影响 34 行
|
-- 方案B（完全对齐）：lib=2 → 2，lib=1/NULL → 1（不写 NULL）
|
-- → 实测影响 86 行（额外 52 行是把 task 里有、lib 里没有的默认去掉）
|
-- 方案C（严格镜像）：连 NULL 一起照抄
|
-- → 实测影响 10867 行，其中 10782 行会被置成 NULL，通常不建议
|
--
|
-- 执行前请先跑第 0 步备份、第 1 步预检。
|
-- ============================================================================
|
|
|
-- ---------------------------------------------------------------------------
|
-- 0. 备份（只备份会被改动的那几列，整表太大没必要）
|
-- ---------------------------------------------------------------------------
|
CREATE TABLE IF NOT EXISTS bak_svy_task_targetoption_dv_20261106 AS
|
SELECT id,
|
taskid,
|
templateID,
|
scriptid,
|
optioncontent,
|
default_value
|
FROM svy_task_template_targetoption
|
WHERE del_flag = '0';
|
|
-- 行数应与下面一致（实测 17403 行）
|
SELECT COUNT(*) AS bak_rows FROM bak_svy_task_targetoption_dv_20261106;
|
|
-- 变更前分布（实测：1=16397，2=1005，4=1）
|
SELECT default_value, COUNT(*) AS cnt
|
FROM svy_task_template_targetoption
|
WHERE del_flag = '0'
|
GROUP BY default_value
|
ORDER BY cnt DESC;
|
|
|
-- ---------------------------------------------------------------------------
|
-- 1. 预检：看看 lib 的默认值同步到 task 会改多少行、改成什么
|
-- ---------------------------------------------------------------------------
|
-- 1.1 汇总（按方案A/B/C 分别预估）
|
SELECT '方案A 只推 lib=2' AS plan,
|
SUM(ld.dv = 2 AND COALESCE(o.default_value, 0) <> 2) AS will_change
|
FROM svy_task_template_targetoption o
|
JOIN svy_task_template_script s ON s.id = o.scriptid AND s.del_flag = '0'
|
JOIN svy_task_template tt ON tt.id = s.templateid AND tt.del_flag = '0'
|
JOIN svy_lib_template lt ON lt.svyid = tt.templateid AND lt.del_flag = '0'
|
JOIN (
|
SELECT l.svyid, l.script_content, lo.optioncontent, MAX(lo.default_value) AS dv
|
FROM svy_lib_template_script l
|
JOIN svy_lib_template_targetoption lo ON lo.scriptid = l.id AND lo.del_flag = '0'
|
WHERE l.del_flag = '0'
|
GROUP BY l.svyid, l.script_content, lo.optioncontent
|
) ld ON ld.svyid = lt.svyid AND ld.script_content = s.script_content AND ld.optioncontent = o.optioncontent
|
WHERE o.del_flag = '0'
|
UNION ALL
|
SELECT '方案B 1/2 完全对齐',
|
SUM(COALESCE(o.default_value, 1) <> (CASE WHEN ld.dv = 2 THEN 2 ELSE 1 END))
|
FROM svy_task_template_targetoption o
|
JOIN svy_task_template_script s ON s.id = o.scriptid AND s.del_flag = '0'
|
JOIN svy_task_template tt ON tt.id = s.templateid AND tt.del_flag = '0'
|
JOIN svy_lib_template lt ON lt.svyid = tt.templateid AND lt.del_flag = '0'
|
JOIN (
|
SELECT l.svyid, l.script_content, lo.optioncontent, MAX(lo.default_value) AS dv
|
FROM svy_lib_template_script l
|
JOIN svy_lib_template_targetoption lo ON lo.scriptid = l.id AND lo.del_flag = '0'
|
WHERE l.del_flag = '0'
|
GROUP BY l.svyid, l.script_content, lo.optioncontent
|
) ld ON ld.svyid = lt.svyid AND ld.script_content = s.script_content AND ld.optioncontent = o.optioncontent
|
WHERE o.del_flag = '0'
|
UNION ALL
|
SELECT '方案C 严格镜像(含NULL)',
|
SUM(COALESCE(o.default_value, -1) <> COALESCE(ld.dv, -1))
|
FROM svy_task_template_targetoption o
|
JOIN svy_task_template_script s ON s.id = o.scriptid AND s.del_flag = '0'
|
JOIN svy_task_template tt ON tt.id = s.templateid AND tt.del_flag = '0'
|
JOIN svy_lib_template lt ON lt.svyid = tt.templateid AND lt.del_flag = '0'
|
JOIN (
|
SELECT l.svyid, l.script_content, lo.optioncontent, MAX(lo.default_value) AS dv
|
FROM svy_lib_template_script l
|
JOIN svy_lib_template_targetoption lo ON lo.scriptid = l.id AND lo.del_flag = '0'
|
WHERE l.del_flag = '0'
|
GROUP BY l.svyid, l.script_content, lo.optioncontent
|
) ld ON ld.svyid = lt.svyid AND ld.script_content = s.script_content AND ld.optioncontent = o.optioncontent
|
WHERE o.del_flag = '0';
|
|
-- 1.2 逐行差异清单（改动前先看一眼，确认没有不该改的）
|
SELECT o.id AS task_option_id,
|
o.templateid AS task_template,
|
tt.svyname,
|
LEFT(s.script_content, 30) AS question,
|
o.optioncontent,
|
o.default_value AS task_now,
|
ld.dv AS lib_value
|
FROM svy_task_template_targetoption o
|
JOIN svy_task_template_script s ON s.id = o.scriptid AND s.del_flag = '0'
|
JOIN svy_task_template tt ON tt.id = s.templateid AND tt.del_flag = '0'
|
JOIN svy_lib_template lt ON lt.svyid = tt.templateid AND lt.del_flag = '0'
|
JOIN (
|
SELECT l.svyid, l.script_content, lo.optioncontent, MAX(lo.default_value) AS dv
|
FROM svy_lib_template_script l
|
JOIN svy_lib_template_targetoption lo ON lo.scriptid = l.id AND lo.del_flag = '0'
|
WHERE l.del_flag = '0'
|
GROUP BY l.svyid, l.script_content, lo.optioncontent
|
) ld ON ld.svyid = lt.svyid AND ld.script_content = s.script_content AND ld.optioncontent = o.optioncontent
|
WHERE o.del_flag = '0'
|
AND COALESCE(o.default_value, 1) <> (CASE WHEN ld.dv = 2 THEN 2 ELSE 1 END)
|
ORDER BY o.templateid, s.id, o.id;
|
|
-- 1.3 顺带看看哪些 task 选项在库里找不到（题面被改过/新加选项），这些不会被同步
|
SELECT COUNT(*) AS unmapped_option_rows
|
FROM svy_task_template_targetoption o
|
JOIN svy_task_template_script s ON s.id = o.scriptid AND s.del_flag = '0'
|
JOIN svy_task_template tt ON tt.id = s.templateid AND tt.del_flag = '0'
|
JOIN svy_lib_template lt ON lt.svyid = tt.templateid AND lt.del_flag = '0'
|
LEFT JOIN svy_lib_template_script l
|
ON l.svyid = lt.svyid AND l.script_content = s.script_content AND l.del_flag = '0'
|
LEFT JOIN svy_lib_template_targetoption lo
|
ON lo.scriptid = l.id AND lo.del_flag = '0' AND lo.optioncontent = o.optioncontent
|
WHERE o.del_flag = '0'
|
AND lo.id IS NULL;
|
|
|
-- ---------------------------------------------------------------------------
|
-- 2. 方案A（推荐）：把库里的默认选项(=2)同步到任务模板选项
|
-- 实测影响 34 行；不动 task 里已有的 1 / NULL，避免误删任务侧单独配过的默认项
|
-- （MySQL 多表 UPDATE 不能带 ORDER BY / LIMIT，下面的条件已经能定位到行）
|
-- ---------------------------------------------------------------------------
|
UPDATE svy_task_template_targetoption o
|
JOIN svy_task_template_script s ON s.id = o.scriptid AND s.del_flag = '0'
|
JOIN svy_task_template tt ON tt.id = s.templateid AND tt.del_flag = '0'
|
JOIN svy_lib_template lt ON lt.svyid = tt.templateid AND lt.del_flag = '0'
|
JOIN (
|
SELECT l.svyid, l.script_content, lo.optioncontent, MAX(lo.default_value) AS dv
|
FROM svy_lib_template_script l
|
JOIN svy_lib_template_targetoption lo ON lo.scriptid = l.id AND lo.del_flag = '0'
|
WHERE l.del_flag = '0'
|
GROUP BY l.svyid, l.script_content, lo.optioncontent
|
) ld ON ld.svyid = lt.svyid AND ld.script_content = s.script_content AND ld.optioncontent = o.optioncontent
|
SET o.default_value = 2
|
WHERE o.del_flag = '0'
|
AND ld.dv = 2
|
AND COALESCE(o.default_value, 0) <> 2;
|
|
|
-- ---------------------------------------------------------------------------
|
-- 3. 方案B：和库完全对齐（lib=2 → 2，lib=1/NULL → 1），实测影响 86 行
|
-- 注意：会把 task 里「有默认、但库里没标默认」的 52 行改成 1
|
-- ---------------------------------------------------------------------------
|
-- UPDATE svy_task_template_targetoption o
|
-- JOIN svy_task_template_script s ON s.id = o.scriptid AND s.del_flag = '0'
|
-- JOIN svy_task_template tt ON tt.id = s.templateid AND tt.del_flag = '0'
|
-- JOIN svy_lib_template lt ON lt.svyid = tt.templateid AND lt.del_flag = '0'
|
-- JOIN (
|
-- SELECT l.svyid, l.script_content, lo.optioncontent, MAX(lo.default_value) AS dv
|
-- FROM svy_lib_template_script l
|
-- JOIN svy_lib_template_targetoption lo ON lo.scriptid = l.id AND lo.del_flag = '0'
|
-- WHERE l.del_flag = '0'
|
-- GROUP BY l.svyid, l.script_content, lo.optioncontent
|
-- ) ld ON ld.svyid = lt.svyid AND ld.script_content = s.script_content AND ld.optioncontent = o.optioncontent
|
-- SET o.default_value = CASE WHEN ld.dv = 2 THEN 2 ELSE 1 END
|
-- WHERE o.del_flag = '0'
|
-- AND COALESCE(o.default_value, 1) <> (CASE WHEN ld.dv = 2 THEN 2 ELSE 1 END);
|
|
|
-- ---------------------------------------------------------------------------
|
-- 4. 方案C：严格镜像库里的值（连 NULL 一起照抄），实测影响 10867 行，
|
-- 其中 10782 行会被置成 NULL（库里这些选项本身没设默认值）。一般不建议。
|
-- ---------------------------------------------------------------------------
|
-- UPDATE svy_task_template_targetoption o
|
-- JOIN svy_task_template_script s ON s.id = o.scriptid AND s.del_flag = '0'
|
-- JOIN svy_task_template tt ON tt.id = s.templateid AND tt.del_flag = '0'
|
-- JOIN svy_lib_template lt ON lt.svyid = tt.templateid AND lt.del_flag = '0'
|
-- JOIN (
|
-- SELECT l.svyid, l.script_content, lo.optioncontent, MAX(lo.default_value) AS dv
|
-- FROM svy_lib_template_script l
|
-- JOIN svy_lib_template_targetoption lo ON lo.scriptid = l.id AND lo.del_flag = '0'
|
-- WHERE l.del_flag = '0'
|
-- GROUP BY l.svyid, l.script_content, lo.optioncontent
|
-- ) ld ON ld.svyid = lt.svyid AND ld.script_content = s.script_content AND ld.optioncontent = o.optioncontent
|
-- SET o.default_value = ld.dv
|
-- WHERE o.del_flag = '0'
|
-- AND COALESCE(o.default_value, -1) <> COALESCE(ld.dv, -1);
|
|
|
-- ---------------------------------------------------------------------------
|
-- 5. 收窄范围示例（只想动某几个任务模板时，在 WHERE 里加条件即可）
|
-- 任务模板 id 来自 svy_task_template.id（= svy_task_template_script.templateid）
|
-- ---------------------------------------------------------------------------
|
-- 5.1 只同步任务模板 514（全院科满意度；实测已一致，改 0 行）
|
-- UPDATE svy_task_template_targetoption o
|
-- JOIN svy_task_template_script s ON s.id = o.scriptid AND s.del_flag = '0'
|
-- JOIN svy_task_template tt ON tt.id = s.templateid AND tt.del_flag = '0'
|
-- JOIN svy_lib_template lt ON lt.svyid = tt.templateid AND lt.del_flag = '0'
|
-- JOIN (
|
-- SELECT l.svyid, l.script_content, lo.optioncontent, MAX(lo.default_value) AS dv
|
-- FROM svy_lib_template_script l
|
-- JOIN svy_lib_template_targetoption lo ON lo.scriptid = l.id AND lo.del_flag = '0'
|
-- WHERE l.del_flag = '0'
|
-- GROUP BY l.svyid, l.script_content, lo.optioncontent
|
-- ) ld ON ld.svyid = lt.svyid AND ld.script_content = s.script_content AND ld.optioncontent = o.optioncontent
|
-- SET o.default_value = 2
|
-- WHERE o.del_flag = '0'
|
-- AND ld.dv = 2
|
-- AND COALESCE(o.default_value, 0) <> 2
|
-- AND s.templateid IN (514);
|
|
-- 5.2 只同步某个机构下的任务模板（orgid 在两个 option 表上都有）
|
-- AND o.orgid = ' xxx '
|
|
|
-- ---------------------------------------------------------------------------
|
-- 6. 校验
|
-- ---------------------------------------------------------------------------
|
-- 6.1 改动后分布（方案A 之后应为：1=16363，2=1039，4=1）
|
SELECT default_value, COUNT(*) AS cnt
|
FROM svy_task_template_targetoption
|
WHERE del_flag = '0'
|
GROUP BY default_value
|
ORDER BY cnt DESC;
|
|
-- 6.2 仍与库不一致的行数（方案A 之后应为 10833，差的都是 lib 侧未设置/为1 的行）
|
SELECT COUNT(*) AS still_diff
|
FROM svy_task_template_targetoption o
|
JOIN svy_task_template_script s ON s.id = o.scriptid AND s.del_flag = '0'
|
JOIN svy_task_template tt ON tt.id = s.templateid AND tt.del_flag = '0'
|
JOIN svy_lib_template lt ON lt.svyid = tt.templateid AND lt.del_flag = '0'
|
JOIN (
|
SELECT l.svyid, l.script_content, lo.optioncontent, MAX(lo.default_value) AS dv
|
FROM svy_lib_template_script l
|
JOIN svy_lib_template_targetoption lo ON lo.scriptid = l.id AND lo.del_flag = '0'
|
WHERE l.del_flag = '0'
|
GROUP BY l.svyid, l.script_content, lo.optioncontent
|
) ld ON ld.svyid = lt.svyid AND ld.script_content = s.script_content AND ld.optioncontent = o.optioncontent
|
WHERE o.del_flag = '0'
|
AND COALESCE(o.default_value, 0) <> COALESCE(ld.dv, 0);
|
|
-- 6.3 每题「默认选项的个数」分布：正常应最多 1 个。
|
-- 实测跑完方案A 后：0 个=5624 题，1 个=1017 题，2 个=8 题，3 个=2 题
|
-- （库里本身就有 1 道题挂了 2 个默认项；带 2/3 个默认项的题目属于同步前就存在的问题）
|
SELECT c AS default_options_per_question, COUNT(*) AS questions
|
FROM (SELECT s.id, SUM(o.default_value = 2) c
|
FROM svy_task_template_script s
|
JOIN svy_task_template_targetoption o ON o.scriptid = s.id AND o.del_flag = '0'
|
WHERE s.del_flag = '0'
|
GROUP BY s.id) x
|
GROUP BY c
|
ORDER BY c;
|
|
|
-- ---------------------------------------------------------------------------
|
-- 7. 回滚（只把被改过的那几列写回备份值）
|
-- ---------------------------------------------------------------------------
|
-- UPDATE svy_task_template_targetoption o
|
-- JOIN bak_svy_task_targetoption_dv_20261106 b ON b.id = o.id
|
-- SET o.default_value = b.default_value
|
-- WHERE o.del_flag = '0';
|
--
|
-- 确认没问题后再删备份表：
|
-- DROP TABLE bak_svy_task_targetoption_dv_20261106;
|
