2019-08-14: 重症肺炎完成血痰培养

insert ETL_0730_ZY_WXS.`cap_statistics_qc_common`(
    uuid,
  patient_guid,
  patient_name,
  patient_gender,
  patient_age,
  patient_age_type,
  
    inpatient_department,   
    doctor_name,
    admission_time,
    discharge_time,
    project_id,
    project_name,
    project_status
)
-- 获取满足重症肺炎完成血/痰培养的患儿信息                              
select detail.uuid, detail.patient_guid, detail.patient_name, detail.patient_gender, detail.patient_age, detail.patient_age_type,
       detail.inpatient_department, detail.doctor_name, detail.admission_date, detail.discharge_date, NULL, 
             "重症肺炎完成血/痰培养" as project_name, 1 as project_status
from mid_patient_info_t as mpit
join yd_patient_11_basic_info as detail
on mpit.uuid=detail.uuid
where (mpit.first_diagnosis_list like "%社区获得性肺炎，重症%" or mpit.first_diagnosis_list like "%重症肺炎%") and detail.patient_age<18
and detail.uuid in
(
select distinct part1.uuid from
        (
        -- 分子条件1
        select concat(customer_id, "-", record_id) as uuid from pt_patient_test_advice 
        where test_name in ("血培养", "血培养+药敏", "血培养及鉴定")
        ) as part1
inner join
        (
        -- 分子条件2
        select concat(customer_id, "-", record_id) as uuid from pt_patient_test_advice 
        where test_name in ("痰咽拭子细菌培养", "痰培养", "痰革兰氏染色和细菌培养", "痰细菌培养+药敏", "痰培养+药敏")
        ) as part2
on part1.uuid=part2.uuid
)