线上的zip 文件导出来了
plan_replayer.zip (2.1 MB)
执行的sql:
Insert Into index_prescription(orgId,orgName,pre_code,serial_code,pre_type,recipel_type,business_date,patient_id,patient_name,sex,card_type,card_code,birth_date,doc_name,dep_name,deploy_doc_name,check_doc_name,dispensing_doc_name,examine_doc_name,tcm_take_means,tcm_decoction_method,antibiotic,infusion_1,infusion_2,infusion_3,union_drug,base_drug,infusion,pre_amount,diag_name,drug_name)
select
cf.MEDICAL_INSTITUT_CODE as orgId,
cf.ORG_NAME as orgName,
cf.PRESCRIPTION_NUMBER as pre_code,
– 处方号
cf.VISITING_SERIAL_NUMBER as serial_code,
– 门诊号
cf.PRESCRIPTION_PROPERTY as pre_type,
– 处方类型
ifnull(cf.recipel_type, ‘1’) as recipel_type,
– 处方种类
cf.PRE_TIME as business_date,
cf.FILE_NUMBER as patient_id,
– 病人id
br.patient_name,
– 病人姓名
br.GENDER_CODE as sex,
– 性别代码
br.ID_TYPE as card_type,
– 证件类型
br.ID_NUMBER as card_code,
– 证件号码
br.BIRTH_DATE as birth_date,
– 出生日期
cf.PRE_DOCTOR_NAME as doc_name,
– 开方医生,
cf.VISIT_DEP_NAME as dep_name,
– 开方科室
cf.DEPLOY_PHARMACIST_NAME as deploy_doc_name,
– 调配药师
cf.CHECK_PHARMACIST_NAME as check_doc_name,
– 核对药师
cf.ISSUE_PHARMACIST_NAME as dispensing_doc_name,
– 发药药师
cf.PRE_CHECK_DOC_CODE as examine_doc_name,
– 审核药师
cf.tcm_take_means,
– 中医服法
cf.tcm_decoction_method,
– 中药煎煮法代码
antibiotic,
infusion_1,
infusion_2,
infusion_3,
union_drug,
base_drug,
infusion,
pre_amount,
– max(zd.diag_name) as diag_name, – 诊断名称
zd.diag_name as diag_name,
mx.drug_name
from
yiliao.TB_CIS_OP_PRESCRIPTION cf use INDEX(PRIMARY)
inner join
(
select
/*+ AGG_TO_COP() */
mx.PRESCRIPTION_NUMBER,
mx.MEDICAL_INSTITUT_CODE,
GROUP_CONCAT(distinct mx.ANTIBIOTIC_SIGN order by mx.ANTIBIOTIC_SIGN) as antibiotic,
– 抗生素标志
max(case when mx.ANTIBIOTIC_SIGN = ‘1’ then 1 else 0 end) as infusion_1,
– 非限制使用的抗生素
max(case when mx.ANTIBIOTIC_SIGN = ‘2’ then 1 else 0 end) as infusion_2,
– 限制使用的抗生素
max(case when mx.ANTIBIOTIC_SIGN = ‘3’ then 1 else 0 end) as infusion_3,
– 特殊使用的抗生素
count(case when mx.ANTIBIOTIC_SIGN != ‘0’ then mx.id else null end) as union_drug,
– 联合用药
GROUP_CONCAT(distinct ifnull(mx.DRUG_BASE_ATTR_CODE, ‘1’) order by mx.DRUG_BASE_ATTR_CODE) as base_drug,
– 药物基药属性
max(case when mx.DRUG_USE_MEANS_CODE = ‘404’ then 1 else 0 end) as infusion,
– 是否输液处方
sum(mx.DRUG_AMOUNT) as pre_amount,
– 处方金额
GROUP_CONCAT(distinct mx.ITEM_NAME) as drug_name
– 药品名称集合
from
yiliao.TB_CIS_OP_PRESCRIPTION_DETAIL mx
where
mx.PRE_TIME >= ‘2021-01-01 00:00:00’
and mx.PRE_TIME < ‘2021-01-31 23:59:59’
group by
mx.MEDICAL_INSTITUT_CODE,
mx.PRESCRIPTION_NUMBER
) mx
on
cf.PRESCRIPTION_NUMBER = mx.PRESCRIPTION_NUMBER
and cf.MEDICAL_INSTITUT_CODE = mx.MEDICAL_INSTITUT_CODE
inner join
(
select
pat.FILE_NUMBER,
pat.patient_name,
– 病人姓名
pat.GENDER_CODE ,
– 性别代码
pat.ID_TYPE ,
– 证件类型
pat.ID_NUMBER ,
– 证件号码
date(pat.BIRTH_DATE) as BIRTH_DATE
– 出生日期
from
yiliao.TB_CIS_PATIENT_INFO pat
##use index(PRIMARY)
where
FILE_NUMBER in (
select
FILE_NUMBER
from
yiliao.TB_CIS_OP_PRESCRIPTION_DETAIL mx
where
mx.PRE_TIME >= ‘2021-01-01 00:00:00’
and mx.PRE_TIME < ‘2021-01-31 23:59:59’
)
)
br on
cf.FILE_NUMBER = br.FILE_NUMBER
left join index_diagnosis zd USE index(index_diagnosis_serial_code_IDX) on
cf.MEDICAL_INSTITUT_CODE = zd.orgid
and cf.VISITING_SERIAL_NUMBER = zd.serial_code