2019-08-10

插入小儿肺炎的数据到yd的中间表

insert indicator_statistics.`yd_patient_11_basic_info`(
    uuid,
    patient_guid,
    patient_name,
    patient_gender,
    patient_age,
    patient_age_type,
    inpatient_department,
    
    doctor_name,
    admission_date,
    discharge_date,
    diagnose_source_name,
    profile_part_type,
    profile_part_name,
    first_diagnose_disease_id,
    first_diagnose_disease_name,
    disease_class_id,
    disease_class_name,
    diagnose_class_name,
        belonged_category
)
select uuid, patient_guid, patient_name, patient_gender, patient_age, patient_age_type, admission_dept_name, doctor_name,
       admission_date, discharge_date, NULL, NULL, NULL, NULL, NULL, NULL, "小儿肺炎", disease_name, NULL 
from mid_patient_info_t
where disease_name like "%肺炎%" and patient_age<18;

-- 调整病例基础信息表，给某些uuid打肺部病变标签，供后续统计
UPDATE indicator_statistics.`yd_patient_11_basic_info`
SET belonged_category='严重肺部病变'
where uuid in (
            SELECT distinct concat(customer_id, "-", record_id) as uuid
            from pt_patient_examination_item
            where examination_item_name like "%肺不张%" 
                    or examination_item_name like "%胸腔积液%" 
                    or examination_item_name like "%肺实变%" 
                    or 
                        (examination_item_name like "%肺叶受累%" 
                         and (examination_item_value_attributive like "%双侧%" or examination_item_value_attributive like "%多侧%")
                        )
);

-- 调整病例基础信息表，给某些uuid打重症肺炎标签，供后续统计
UPDATE indicator_statistics.`yd_patient_11_basic_info`
SET belonged_category="重症肺炎"
where uuid in (
                select distinct uuid from mid_patient_info_t 
          where disease_name like "%社区获得性肺炎,重症%" or disease_name like "%重症肺炎%"
          union
          -- 分子part2
                select part1.uuid from
                (
                    select distinct CONCAT(customer_id, "-", record_id) as uuid
                    from pt_patient_assess 
                    where assess_name like "%cap严重程度评估%" and assess_value='重度'
                ) as part1
);

-- 调整病例基础信息表，给某些uuid打完成实验室检查的标签，供后续统计使用
UPDATE indicator_statistics.`yd_patient_11_basic_info`
SET belonged_category="完成实验室检查"
where uuid in (
     select part1.uuid as uuid from
         (
            -- 分母条件的分子1统计
            select numerator1.uuid as uuid from
            (
                select DISTINCT D.uuid from
                -- 分子1
                (select A.uuid from
                    (select CONCAT(customer_id, "-", record_id) as uuid from pt_patient_test_advice where test_name like "%血沉%") as A
                    join
                  (select CONCAT(customer_id, "-", record_id) as uuid from pt_patient_test_advice where test_name like "%降钙素原%") as B
              on A.uuid=B.uuid
                ) as C
                join
                (select CONCAT(customer_id, "-", record_id) as uuid from pt_patient_test_advice where test_name like "%超敏C反应蛋白%") as D
                on D.uuid = C.uuid
            ) as numerator1 
            join 
            (
                -- 分母条件
                select distinct uuid from mid_patient_info_t
                where disease_name like "%肺炎%" and patient_age<18
            ) as denominator on denominator.uuid=numerator1.uuid
        ) as part1
        union
        -- 分母条件下的分子2统计
        select distinct A.uuid  from
        (
            SELECT distinct concat(a.customer_id, "-", a.record_id) as uuid, a.disease_id, a.item, a.rule_id, a.result, a.item_id AS project_id, 
                CASE          
                    WHEN ( max( result ) = 1 ) and (min(result)=-1) and GROUP_CONCAT(result) not like "%0%" THEN
                    1 ELSE - 1 
            END final_result 
            FROM
                    qc_patient_item AS a
                    where a.item like "%ESR检测%" or a.item like "%CRP检测%" or a.item like "%PCT检测%"
            GROUP BY
                    record_id,
                    disease_id,
                    rule_id,
                    item_id
            ORDER BY final_result desc
        ) as A where A.final_result=1
);

-- 调整病例基础信息表，给某些uuid打完成血痰培养标签，供后续统计使用
UPDATE indicator_statistics.`yd_patient_11_basic_info`
SET belonged_category="完成血痰培养"
where uuid in (
    select detail.uuid from 
    (
        -- 分母
        select * from mid_patient_info_t 
        where (disease_name like "%社区获得性肺炎,重症%" or disease_name like "%重症肺炎%") and patient_age<18
    ) as detail
    inner join
    (
        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
    ) as part3
);

-- 调整病例基础信息表，给某些uuid打死亡标签，供后续统计使用
UPDATE indicator_statistics.`yd_patient_11_basic_info`
SET belonged_category="死亡"
where uuid in (
    SELECT uuid FROM mid_patient_info_t as detail
    where discharge_treat_effect="死亡"
)