-- ============================================================================
|
-- 南华附一（nhfy）：恢复被逻辑删除的满意度作答明细（2026-10-21 已执行）
|
--
|
-- 【背景】导出「问题详情占比」比「患者随访信息表单」少 10 人（370 vs 380），
|
-- 原因是这 10 个子任务的 service_subtask_detail 全部被置 del_flag='1'
|
-- （同一患者还有 2~6 份重复副本，答案完全一致）。
|
-- 详见 / 排查_问题占比与随访表单人数不一致.sql、抓现场_谁把明细置为删除_20261021.sql
|
--
|
-- 【本次执行内容】
|
-- 1) 恢复 5 个子任务（45108 45109 45110 45111 45114）：每 (sub_id, scriptid) 取 MAX(id) 那一份置 del_flag='0'
|
-- -> 更新 95 行（19 题 × 5 人）
|
-- 2) 补齐此前人工恢复漏掉的题（45099 缺 4 题、45102 缺 9 题）
|
-- -> 更新 13 行
|
-- 3) 未改 update_time（保持 create_time=update_time 的取证指纹，也与此前人工恢复的行一致）
|
-- 4) 重复副本（同题同答案的多份）仍保留 del_flag='1'，不影响统计：
|
-- 导出 SQL 按 (task, question, asrtext, targetvalue, value_type) 分组 + COUNT(DISTINCT s.id) 去重
|
--
|
-- 【执行结果（2026-10-21 复核）】
|
-- 表单行数 380 / 有有效明细的患者 380；19 道题逐题人数全部 380（此前 370/378/379）
|
-- 每个子任务活行题目数：44957(19) 45094(19) 45095(19) 45099(19) 45102(19)
|
-- 45108(19) 45109(19) 45110(19) 45111(19) 45114(19)
|
--
|
-- 【备份表】用于回滚
|
-- bak_ssd_20261021_nhfy = 412 行（45108/45109/45110/45111/45114 的全部明细）
|
-- bak_ssd_20261021_nhfy_2 = 114 行（45099/45102 的全部明细）
|
-- ============================================================================
|
|
|
-- ----------------------------------------------------------------------------
|
-- 【已执行】(1) 备份
|
-- ----------------------------------------------------------------------------
|
-- DROP TABLE IF EXISTS bak_ssd_20261021_nhfy;
|
-- CREATE TABLE bak_ssd_20261021_nhfy AS
|
-- SELECT * FROM service_subtask_detail WHERE sub_id IN (45108, 45109, 45110, 45111, 45114);
|
|
-- DROP TABLE IF EXISTS bak_ssd_20261021_nhfy_2;
|
-- CREATE TABLE bak_ssd_20261021_nhfy_2 AS
|
-- SELECT * FROM service_subtask_detail WHERE sub_id IN (45099, 45102);
|
|
|
-- ----------------------------------------------------------------------------
|
-- 【已执行】(2) 恢复 5 个子任务：每 (sub_id, scriptid) 留最新那份 -> 95 行
|
-- ----------------------------------------------------------------------------
|
-- UPDATE service_subtask_detail d
|
-- JOIN (SELECT sub_id, scriptid, MAX(id) AS keep_id
|
-- FROM service_subtask_detail
|
-- WHERE sub_id IN (45108, 45109, 45110, 45111, 45114)
|
-- GROUP BY sub_id, scriptid) x ON x.keep_id = d.id
|
-- SET d.del_flag = '0';
|
|
|
-- ----------------------------------------------------------------------------
|
-- 【已执行】(3) 补齐 45099 / 45102 缺的题 -> 13 行
|
-- ----------------------------------------------------------------------------
|
-- UPDATE service_subtask_detail d
|
-- JOIN (SELECT x.sub_id, x.scriptid, MAX(x.id) AS keep_id
|
-- FROM service_subtask_detail x
|
-- WHERE x.sub_id IN (45099, 45102)
|
-- AND NOT EXISTS (SELECT 1 FROM service_subtask_detail d3
|
-- WHERE d3.sub_id = x.sub_id AND d3.scriptid = x.scriptid AND d3.del_flag = 0)
|
-- GROUP BY x.sub_id, x.scriptid) k ON k.keep_id = d.id
|
-- SET d.del_flag = '0';
|
|
|
-- ----------------------------------------------------------------------------
|
-- 【校验】随时可重跑（预期 380 / 380）
|
-- ----------------------------------------------------------------------------
|
SET @expect := 380;
|
|
SELECT COUNT(*) AS form_rows,
|
COUNT(DISTINCT CASE WHEN d.live = 1 THEN d.sub_id END) AS with_live_detail
|
FROM service_subtask s
|
LEFT JOIN (SELECT sub_id, MAX(del_flag = 0) AS live FROM service_subtask_detail GROUP BY sub_id) d
|
ON d.sub_id = s.id
|
WHERE s.del_flag = '0' AND s.orgid = '20001001' AND s.campusid = '30001002'
|
AND s.service_type = 6 AND s.sendstate = 6 AND s.visit_count = 1
|
AND (s.deptcode = '40003511' OR s.leavehospitaldistrictcode = '40003514');
|
|
SELECT MIN(c) AS min_cnt, MAX(c) AS max_cnt, SUM(c <> @expect) AS not_equal
|
FROM ( SELECT COUNT(DISTINCT s.id) AS c
|
FROM service_subtask s
|
JOIN service_subtask_detail d
|
ON d.sub_id = s.id AND d.del_flag = 0
|
AND d.id = (SELECT MAX(d2.id) FROM service_subtask_detail d2
|
WHERE d2.sub_id = d.sub_id AND d2.questiontext <=> d.questiontext AND d2.del_flag = '0')
|
WHERE s.del_flag = '0' AND s.orgid = '20001001' AND s.campusid = '30001002'
|
AND s.service_type = 6 AND s.sendstate = 6 AND s.visit_count = 1
|
AND (s.deptcode = '40003511' OR s.leavehospitaldistrictcode = '40003514')
|
GROUP BY d.questiontext
|
) x;
|
|
|
-- ----------------------------------------------------------------------------
|
-- 【回滚】(需要撤销时)
|
-- ----------------------------------------------------------------------------
|
-- UPDATE service_subtask_detail d
|
-- JOIN bak_ssd_20261021_nhfy b ON b.id = d.id
|
-- SET d.del_flag = b.del_flag, d.update_time = b.update_time;
|
-- UPDATE service_subtask_detail d
|
-- JOIN bak_ssd_20261021_nhfy_2 b ON b.id = d.id
|
-- SET d.del_flag = b.del_flag, d.update_time = b.update_time;
|
|
|
-- ============================================================================
|
-- 【注意】本次只是修数据。根因（谁把明细置 1）尚未定位：
|
-- 全库被删明细的 update_time 全部等于 create_time，说明删除**没有**走
|
-- Controller->Service 那两条接口（它们会强制 NOW()）；用
|
-- sql/抓现场_谁把明细置为删除_20261021.sql 里的 general_log 抓一次 SQL 原文即可定案。
|
-- 在此之前，只要还有患者"重填/被补默认值"，同样的问题会再次出现。
|
-- ============================================================================
|
