美文网首页
2019-08-09:插入数据到中间表2

2019-08-09:插入数据到中间表2

作者: AI_Finance | 来源:发表于2019-08-09 08:56 被阅读0次
    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
    )
    
    
    
    

    相关文章

      网友评论

          本文标题:2019-08-09:插入数据到中间表2

          本文链接:https://www.haomeiwen.com/subject/elxcjctx.html