84 lines
2.5 KiB
SQL
84 lines
2.5 KiB
SQL
-- ============================================
|
||
-- Bug #318 历史手术医嘱数据修复（执行版本）
|
||
-- ============================================
|
||
|
||
-- 步骤1：先查看有多少条记录需要修复
|
||
SELECT
|
||
'需要修复的记录数' as description,
|
||
COUNT(*) as count
|
||
FROM wor_service_request
|
||
WHERE category_enum = 4
|
||
AND delete_flag = '0'
|
||
AND (content_json::jsonb->>'surgeryName' IS NULL OR content_json::jsonb->>'surgeryName' = '');
|
||
|
||
-- 步骤2：【执行此语句进行修复】
|
||
-- 从 cli_surgery 表关联获取手术名称
|
||
UPDATE wor_service_request wsr
|
||
SET content_json = COALESCE(
|
||
(wsr.content_json::jsonb || jsonb_build_object(
|
||
'surgeryName', cs.surgery_name,
|
||
'surgeryCode', cs.surgery_code
|
||
))::text,
|
||
jsonb_build_object(
|
||
'surgeryName', COALESCE(cs.surgery_name, '手术项目'),
|
||
'surgeryCode', COALESCE(cs.surgery_code, '')
|
||
)::text
|
||
)
|
||
FROM cli_surgery cs
|
||
WHERE wsr.category_enum = 4
|
||
AND wsr.delete_flag = '0'
|
||
AND (wsr.content_json::jsonb->>'surgeryName' IS NULL OR wsr.content_json::jsonb->>'surgeryName' = '')
|
||
AND cs.id = wsr.activity_id
|
||
AND cs.delete_flag = '0';
|
||
|
||
-- 步骤3：【备选修复】对于没有关联 cli_surgery 的记录，使用默认值
|
||
UPDATE wor_service_request
|
||
SET content_json = COALESCE(
|
||
(content_json::jsonb || jsonb_build_object(
|
||
'surgeryName', '手术项目',
|
||
'surgeryCode', ''
|
||
))::text,
|
||
jsonb_build_object(
|
||
'surgeryName', '手术项目',
|
||
'surgeryCode', ''
|
||
)::text
|
||
)
|
||
WHERE category_enum = 4
|
||
AND delete_flag = '0'
|
||
AND (content_json::jsonb->>'surgeryName' IS NULL OR content_json::jsonb->>'surgeryName' = '');
|
||
|
||
-- 步骤4：验证修复结果
|
||
SELECT
|
||
'修复后剩余空记录数' as description,
|
||
COUNT(*) as count
|
||
FROM wor_service_request
|
||
WHERE category_enum = 4
|
||
AND delete_flag = '0'
|
||
AND (content_json::jsonb->>'surgeryName' IS NULL OR content_json::jsonb->>'surgeryName' = '');
|
||
|
||
-- 步骤5：查看修复后的示例数据
|
||
SELECT
|
||
id,
|
||
activity_id,
|
||
content_json::jsonb->>'surgeryName' as surgery_name,
|
||
content_json::jsonb->>'surgeryCode' as surgery_code,
|
||
create_time
|
||
FROM wor_service_request
|
||
WHERE category_enum = 4
|
||
AND delete_flag = '0'
|
||
ORDER BY create_time DESC
|
||
LIMIT 5;
|
||
|
||
|
||
-- 查看今天新插入的手术医嘱
|
||
SELECT
|
||
id,
|
||
content_json::jsonb->>'surgeryName' as surgery_name,
|
||
content_json::jsonb->>'surgeryCode' as surgery_code,
|
||
create_time
|
||
FROM wor_service_request
|
||
WHERE category_enum = 4
|
||
AND delete_flag = '0'
|
||
AND create_time >= CURRENT_DATE
|
||
ORDER BY create_time DESC
|
||
LIMIT 3; |