2019-08-08

根据入院记录的初步诊断中，获取解析后得到的第一诊断信息，作为病种病例的筛选sql

select distinct part3.*, ppd.disease_name from
(
    select part1.uuid, part1.record_name, part1.attribute_name, part1.progress_content, part2.patient_age, part2.patient_age_type, 
                 part2.patient_gender, part2.inpatient_department from 
    (
            select uuid, record_name, attribute_name, progress_content from
                            (
                                                            select concat(customer_id,'-',record_id) as uuid, record_type_id, msda.attribute_name, progress_content
                                                            from pt_patient_progress ppp
                                                            join ms_dictionary_attribute msda
                                                            on ppp.dictionary_attribute_id = msda.id where customer_id=1172
                            ) result_attr
             join ms_system_record_type msr
             on result_attr.record_type_id = msr.record_type
             where record_name in ("入院记录", "出院小结", "首次病程")
     ) as part1
    join 
    (
            select uuid, patient_age, patient_age_type, patient_gender, inpatient_department  
            from hmcdss2_xw_0730.mt_patient_record 
--             where inpatient_department like "%呼吸%" 
    ) as part2
    on  part1.uuid=part2.uuid
) as part3
join pt_patient_disease as ppd
on concat(ppd.customer_id, "-", record_id)=part3.uuid
where ppd.profile_part_type=22 and ppd.record_type_id=1 and ppd.dictionary_attribute_id=86 
      and ppd.disease_name="慢性阻塞性肺病伴有急性加重"