-- ============================================================================
|
-- 把 pat_med_inhosp 的「科室 / 病区」回填到 service_subtask（在院 4 列 + 离院 4 列）
|
-- 生成日期：2026-11-06（v2：修正 join 键 + 合并离院维度）
|
-- 适用：MySQL 8.0 / 5.7（未用 CTE、窗口函数）
|
--
|
-- ⚠️ 前置条件：先执行过 sql/rename_service_subtask_dims_20261106.sql（列已改名）。
|
--
|
-- ⚠️⚠️ join 键（v1 写错过，这里更正）：
|
-- service_subtask.inhospid 存的是 **pat_med_inhosp.inhospid**（业务主键），不是 id。
|
-- 证据：VisitInhospMapper.xml#selectForScan 查的是 b.inhospid；
|
-- VisitSubtaskFactory 里是 d.setInhospid(inhosp.getInhospid())。
|
-- 门诊侧例外：PatMedOuthospServiceDelegatingImpl 用的是 pat_med_outhosp.getId()，
|
-- 所以 pat_med_outhosp 的 id 会和 pat_med_inhosp.inhospid 撞号 → 必须排除 service_type='3'。
|
--
|
-- 字段映射（8 列，注意「在院病区」和「出院病区」不是一回事）
|
-- 在院维度（本次新增列）
|
-- service_subtask.hospitaldistrictcode/name ← pat_med_inhosp.hospitaldistrictcode/name （在院病区）
|
-- service_subtask.deptcode/name ← pat_med_inhosp.deptcode/name （在院科室）
|
-- 离院/出院维度（老列，原 deptcode/deptname 改名而来）
|
-- service_subtask.leaveldeptcode/name ← pat_med_inhosp.leaveldeptcode/name （出院科室）
|
-- service_subtask.leavehospitaldistrictcode/name ← pat_med_inhosp.leavehospitaldistrictcode/name （出院病区）
|
--
|
-- 安全策略
|
-- 1) 全部用 COALESCE「目标列为空才写」→ **不覆盖任何已有值**，可重复执行；
|
-- 2) 更新前先备份受影响行的这 8 列旧值（§2）；
|
-- 3) 大表分批模板见 §6。
|
-- ============================================================================
|
|
|
-- ----------------------------------------------------------------------------
|
-- 0) 前置自查
|
-- ----------------------------------------------------------------------------
|
SHOW FULL COLUMNS FROM service_subtask LIKE '%dept%';
|
SHOW FULL COLUMNS FROM service_subtask LIKE 'hospitaldistrict%';
|
SHOW FULL COLUMNS FROM pat_med_inhosp LIKE '%dept%';
|
SHOW FULL COLUMNS FROM pat_med_inhosp LIKE 'hospitaldistrict%';
|
|
|
-- ----------------------------------------------------------------------------
|
-- 1) 影响面评估（先看数，再动手）
|
-- ----------------------------------------------------------------------------
|
|
-- 1.1 能 join 上的子任务数 + 8 列里各有多少是空的
|
SELECT COUNT(*) AS joined_rows,
|
SUM(hospitaldistrictcode IS NULL OR hospitaldistrictcode = '') AS hd_code_empty,
|
SUM(hospitaldistrictname IS NULL OR hospitaldistrictname = '') AS hd_name_empty,
|
SUM(deptcode IS NULL OR deptcode = '') AS dept_code_empty,
|
SUM(deptname IS NULL OR deptname = '') AS dept_name_empty,
|
SUM(leaveldeptcode IS NULL OR leaveldeptcode = '') AS lv_dept_code_empty,
|
SUM(leaveldeptname IS NULL OR leaveldeptname = '') AS lv_dept_name_empty,
|
SUM(leavehospitaldistrictcode IS NULL OR leavehospitaldistrictcode = '') AS lv_hd_code_empty,
|
SUM(leavehospitaldistrictname IS NULL OR leavehospitaldistrictname = '') AS lv_hd_name_empty
|
FROM service_subtask s
|
JOIN pat_med_inhosp b
|
ON b.inhospid = s.inhospid
|
AND b.patid = s.patid
|
WHERE s.inhospid IS NOT NULL
|
AND s.service_type <> '3'
|
AND (s.hosp_type IS NULL OR s.hosp_type <> '8');
|
|
-- 1.2 按 service_type 看分布（2/13 出院随访、17/18 入院宣教/入院随访、6/14/15/16 满意度子任务）
|
SELECT s.service_type,
|
COUNT(*) AS cnt,
|
SUM(s.deptcode IS NULL OR s.deptcode = '') AS dept_empty,
|
SUM(s.hospitaldistrictcode IS NULL OR s.hospitaldistrictcode = '') AS hd_empty,
|
SUM(s.leaveldeptcode IS NULL OR s.leaveldeptcode = '') AS lv_dept_empty,
|
SUM(s.leavehospitaldistrictcode IS NULL OR s.leavehospitaldistrictcode = '') AS lv_hd_empty
|
FROM service_subtask s
|
JOIN pat_med_inhosp b ON b.inhospid = s.inhospid AND b.patid = s.patid
|
WHERE s.inhospid IS NOT NULL
|
AND s.service_type <> '3'
|
GROUP BY s.service_type
|
ORDER BY cnt DESC;
|
|
-- 1.3 join 不上的诊断：inhospid 有值、但 patid 对不上
|
SELECT s.service_type,
|
COUNT(*) AS unmatched
|
FROM service_subtask s
|
LEFT JOIN pat_med_inhosp b
|
ON b.inhospid = s.inhospid
|
AND b.patid = s.patid
|
WHERE s.inhospid IS NOT NULL
|
AND s.service_type <> '3'
|
AND b.inhospid IS NULL
|
GROUP BY s.service_type
|
ORDER BY unmatched DESC;
|
|
-- 1.4 源表 inhospid 是否唯一（不唯一会让 UPDATE 取到其中一行，结果不确定）
|
SELECT inhospid, COUNT(*) AS c, COUNT(DISTINCT patid) AS pats
|
FROM pat_med_inhosp
|
WHERE inhospid IS NOT NULL
|
GROUP BY inhospid
|
HAVING c > 1
|
ORDER BY c DESC
|
LIMIT 20;
|
|
-- 1.5 一条子任务是否会被多行源数据命中（应为 0；有值就先把 §1.4 的重复处理干净）
|
SELECT COUNT(*) AS subtask_rows, COUNT(*) - COUNT(DISTINCT s.id) AS dup_hit
|
FROM service_subtask s
|
JOIN pat_med_inhosp b ON b.inhospid = s.inhospid AND b.patid = s.patid
|
WHERE s.inhospid IS NOT NULL
|
AND s.service_type <> '3';
|
|
|
-- ----------------------------------------------------------------------------
|
-- 2) 备份受影响行的 8 列旧值（回滚见 §7）
|
-- ----------------------------------------------------------------------------
|
CREATE TABLE bak_service_subtask_dept_20261106 AS
|
SELECT s.id,
|
s.service_type,
|
s.hosp_type,
|
s.inhospid,
|
s.patid,
|
s.leaveldeptcode,
|
s.leaveldeptname,
|
s.leavehospitaldistrictcode,
|
s.leavehospitaldistrictname,
|
s.hospitaldistrictcode,
|
s.hospitaldistrictname,
|
s.deptcode,
|
s.deptname
|
FROM service_subtask s
|
JOIN pat_med_inhosp b
|
ON b.inhospid = s.inhospid
|
AND b.patid = s.patid
|
WHERE s.inhospid IS NOT NULL
|
AND s.service_type <> '3'
|
AND (s.hosp_type IS NULL OR s.hosp_type <> '8')
|
AND ( NULLIF(TRIM(s.hospitaldistrictcode), '') IS NULL
|
OR NULLIF(TRIM(s.hospitaldistrictname), '') IS NULL
|
OR NULLIF(TRIM(s.deptcode), '') IS NULL
|
OR NULLIF(TRIM(s.deptname), '') IS NULL
|
OR NULLIF(TRIM(s.leaveldeptcode), '') IS NULL
|
OR NULLIF(TRIM(s.leaveldeptname), '') IS NULL
|
OR NULLIF(TRIM(s.leavehospitaldistrictcode), '') IS NULL
|
OR NULLIF(TRIM(s.leavehospitaldistrictname), '') IS NULL );
|
|
SELECT COUNT(*) AS backup_rows FROM bak_service_subtask_dept_20261106;
|
|
|
-- ----------------------------------------------------------------------------
|
-- 3) 主回填：在院 4 列 + 离院 4 列（只补空，不覆盖；可重复执行）
|
-- ----------------------------------------------------------------------------
|
UPDATE service_subtask s
|
JOIN pat_med_inhosp b
|
ON b.inhospid = s.inhospid
|
AND b.patid = s.patid
|
SET s.hospitaldistrictcode = COALESCE(NULLIF(TRIM(s.hospitaldistrictcode), ''), b.hospitaldistrictcode),
|
s.hospitaldistrictname = COALESCE(NULLIF(TRIM(s.hospitaldistrictname), ''), b.hospitaldistrictname),
|
s.deptcode = COALESCE(NULLIF(TRIM(s.deptcode), ''), b.deptcode),
|
s.deptname = COALESCE(NULLIF(TRIM(s.deptname), ''), b.deptname),
|
s.leaveldeptcode = COALESCE(NULLIF(TRIM(s.leaveldeptcode), ''), b.leaveldeptcode),
|
s.leaveldeptname = COALESCE(NULLIF(TRIM(s.leaveldeptname), ''), b.leaveldeptname),
|
s.leavehospitaldistrictcode = COALESCE(NULLIF(TRIM(s.leavehospitaldistrictcode), ''), b.leavehospitaldistrictcode),
|
s.leavehospitaldistrictname = COALESCE(NULLIF(TRIM(s.leavehospitaldistrictname), ''), b.leavehospitaldistrictname)
|
WHERE s.inhospid IS NOT NULL
|
AND s.service_type <> '3' -- 门诊随访的 inhospid 是 pat_med_outhosp.id
|
AND (s.hosp_type IS NULL OR s.hosp_type <> '8') -- 扫码生成的虚拟患者
|
AND ( NULLIF(TRIM(s.hospitaldistrictcode), '') IS NULL
|
OR NULLIF(TRIM(s.hospitaldistrictname), '') IS NULL
|
OR NULLIF(TRIM(s.deptcode), '') IS NULL
|
OR NULLIF(TRIM(s.deptname), '') IS NULL
|
OR NULLIF(TRIM(s.leaveldeptcode), '') IS NULL
|
OR NULLIF(TRIM(s.leaveldeptname), '') IS NULL
|
OR NULLIF(TRIM(s.leavehospitaldistrictcode), '') IS NULL
|
OR NULLIF(TRIM(s.leavehospitaldistrictname), '') IS NULL );
|
|
|
-- ----------------------------------------------------------------------------
|
-- 4) 口径说明 + 可选的"强制改写"
|
--
|
-- 4.1 为什么离院 4 列也用「只补空」：
|
-- service_type = 17/18（入院宣教/入院随访）生成时走 type 4/5 分支，
|
-- `leaveldeptcode/name` 里存的其实是**入院科室**（d.setLeaveldeptcode(inhosp.getDeptcode())）。
|
-- 这些行非空 → §3 不会动它们；若为空则会被补成 pat_med_inhosp.leaveldeptcode
|
-- （住院患者通常为空 → 等于没变）。
|
-- 出院随访（2/13）与满意度子任务（6/14/15/16）的离院 4 列生成时本来就取自
|
-- pat_med_inhosp，只可能是历史脏数据为空，补空即修数据。
|
--
|
-- 4.2 如果你要的是"17/18 也统一成出院科室口径"，用下面这段（**会覆盖**，先跑 §2 备份）：
|
-- ----------------------------------------------------------------------------
|
-- UPDATE service_subtask s
|
-- JOIN pat_med_inhosp b ON b.inhospid = s.inhospid AND b.patid = s.patid
|
-- SET s.leaveldeptcode = b.leaveldeptcode,
|
-- s.leaveldeptname = b.leaveldeptname,
|
-- s.leavehospitaldistrictcode = b.leavehospitaldistrictcode,
|
-- s.leavehospitaldistrictname = b.leavehospitaldistrictname
|
-- WHERE s.inhospid IS NOT NULL
|
-- AND s.service_type IN ('17', '18')
|
-- AND (NULLIF(TRIM(b.leaveldeptcode), '') IS NOT NULL
|
-- OR NULLIF(TRIM(b.leavehospitaldistrictcode), '') IS NOT NULL);
|
|
|
-- ----------------------------------------------------------------------------
|
-- 5) 【可选】全都强制覆盖版（"原来有值但是错的"时用）
|
-- 把 §3 的 COALESCE(...) 换成直接赋值，并去掉 WHERE 里那串「目标列为空」条件：
|
-- SET s.hospitaldistrictcode = b.hospitaldistrictcode,
|
-- s.hospitaldistrictname = b.hospitaldistrictname,
|
-- s.deptcode = b.deptcode, s.deptname = b.deptname,
|
-- s.leaveldeptcode = b.leaveldeptcode, s.leaveldeptname = b.leaveldeptname,
|
-- s.leavehospitaldistrictcode = b.leavehospitaldistrictcode,
|
-- s.leavehospitaldistrictname = b.leavehospitaldistrictname
|
-- ⚠️ pat_med_inhosp.dept* 会被「在院信息刷新」定时任务按 HIS 当前科室改写，
|
-- 覆盖后可能把"出院当时的科室"改成"现在 HIS 里的科室"。覆盖前务必先备份。
|
-- ----------------------------------------------------------------------------
|
|
|
-- ----------------------------------------------------------------------------
|
-- 6) 大表分批模板（多表 UPDATE 不支持 LIMIT，用 id 区间；每段单独提交）
|
-- 先查范围：SELECT MIN(id), MAX(id) FROM service_subtask;
|
-- ----------------------------------------------------------------------------
|
-- UPDATE service_subtask s
|
-- JOIN pat_med_inhosp b ON b.inhospid = s.inhospid AND b.patid = s.patid
|
-- SET s.hospitaldistrictcode = COALESCE(NULLIF(TRIM(s.hospitaldistrictcode), ''), b.hospitaldistrictcode),
|
-- s.hospitaldistrictname = COALESCE(NULLIF(TRIM(s.hospitaldistrictname), ''), b.hospitaldistrictname),
|
-- s.deptcode = COALESCE(NULLIF(TRIM(s.deptcode), ''), b.deptcode),
|
-- s.deptname = COALESCE(NULLIF(TRIM(s.deptname), ''), b.deptname),
|
-- s.leaveldeptcode = COALESCE(NULLIF(TRIM(s.leaveldeptcode), ''), b.leaveldeptcode),
|
-- s.leaveldeptname = COALESCE(NULLIF(TRIM(s.leaveldeptname), ''), b.leaveldeptname),
|
-- s.leavehospitaldistrictcode = COALESCE(NULLIF(TRIM(s.leavehospitaldistrictcode), ''), b.leavehospitaldistrictcode),
|
-- s.leavehospitaldistrictname = COALESCE(NULLIF(TRIM(s.leavehospitaldistrictname), ''), b.leavehospitaldistrictname)
|
-- WHERE s.id > 1 AND s.id <= 50000
|
-- AND s.inhospid IS NOT NULL
|
-- AND s.service_type <> '3'
|
-- AND (s.hosp_type IS NULL OR s.hosp_type <> '8')
|
-- AND ( NULLIF(TRIM(s.hospitaldistrictcode), '') IS NULL
|
-- OR NULLIF(TRIM(s.hospitaldistrictname), '') IS NULL
|
-- OR NULLIF(TRIM(s.deptcode), '') IS NULL
|
-- OR NULLIF(TRIM(s.deptname), '') IS NULL
|
-- OR NULLIF(TRIM(s.leaveldeptcode), '') IS NULL
|
-- OR NULLIF(TRIM(s.leaveldeptname), '') IS NULL
|
-- OR NULLIF(TRIM(s.leavehospitaldistrictcode), '') IS NULL
|
-- OR NULLIF(TRIM(s.leavehospitaldistrictname), '') IS NULL );
|
|
|
-- ----------------------------------------------------------------------------
|
-- 7) 回滚（按 §2 备份表还原 8 列）
|
-- ----------------------------------------------------------------------------
|
-- UPDATE service_subtask s
|
-- JOIN bak_service_subtask_dept_20261106 k ON k.id = s.id
|
-- SET s.hospitaldistrictcode = k.hospitaldistrictcode,
|
-- s.hospitaldistrictname = k.hospitaldistrictname,
|
-- s.deptcode = k.deptcode,
|
-- s.deptname = k.deptname,
|
-- s.leaveldeptcode = k.leaveldeptcode,
|
-- s.leaveldeptname = k.leaveldeptname,
|
-- s.leavehospitaldistrictcode = k.leavehospitaldistrictcode,
|
-- s.leavehospitaldistrictname = k.leavehospitaldistrictname;
|
|
|
-- ----------------------------------------------------------------------------
|
-- 8) 回填后核对
|
-- ----------------------------------------------------------------------------
|
|
-- 8.1 还剩多少空（应接近 0；剩下的基本是 §1.3/§1.4 命中不了的）
|
SELECT COUNT(*) AS still_empty_rows,
|
SUM(hospitaldistrictcode IS NULL OR hospitaldistrictcode = '') AS hd_code_empty,
|
SUM(hospitaldistrictname IS NULL OR hospitaldistrictname = '') AS hd_name_empty,
|
SUM(deptcode IS NULL OR deptcode = '') AS dept_code_empty,
|
SUM(deptname IS NULL OR deptname = '') AS dept_name_empty,
|
SUM(leaveldeptcode IS NULL OR leaveldeptcode = '') AS lv_dept_code_empty,
|
SUM(leaveldeptname IS NULL OR leaveldeptname = '') AS lv_dept_name_empty,
|
SUM(leavehospitaldistrictcode IS NULL OR leavehospitaldistrictcode = '') AS lv_hd_code_empty,
|
SUM(leavehospitaldistrictname IS NULL OR leavehospitaldistrictname = '') AS lv_hd_name_empty
|
FROM service_subtask s
|
JOIN pat_med_inhosp b ON b.inhospid = s.inhospid AND b.patid = s.patid
|
WHERE s.inhospid IS NOT NULL
|
AND s.service_type <> '3'
|
AND (s.hosp_type IS NULL OR s.hosp_type <> '8')
|
AND ( NULLIF(TRIM(s.hospitaldistrictcode), '') IS NULL
|
OR NULLIF(TRIM(s.hospitaldistrictname), '') IS NULL
|
OR NULLIF(TRIM(s.deptcode), '') IS NULL
|
OR NULLIF(TRIM(s.deptname), '') IS NULL
|
OR NULLIF(TRIM(s.leaveldeptcode), '') IS NULL
|
OR NULLIF(TRIM(s.leaveldeptname), '') IS NULL
|
OR NULLIF(TRIM(s.leavehospitaldistrictcode), '') IS NULL
|
OR NULLIF(TRIM(s.leavehospitaldistrictname), '') IS NULL );
|
|
-- 8.2 逐行对照抽检（两边是否一致）
|
SELECT s.id, s.service_type, s.inhospid,
|
s.deptcode AS sub_dept, b.deptcode AS inhosp_dept,
|
s.hospitaldistrictcode AS sub_hd, b.hospitaldistrictcode AS inhosp_hd,
|
s.leaveldeptcode AS sub_lv_dept, b.leaveldeptcode AS inhosp_lv_dept,
|
s.leavehospitaldistrictcode AS sub_lv_hd, b.leavehospitaldistrictcode AS inhosp_lv_hd
|
FROM service_subtask s
|
JOIN pat_med_inhosp b ON b.inhospid = s.inhospid AND b.patid = s.patid
|
WHERE s.inhospid IS NOT NULL
|
AND s.service_type <> '3'
|
ORDER BY s.id DESC
|
LIMIT 50;
|
