cap

创建细指标明细表

CREATE TABLE if not exists cap_detail_table (
        `id` INT (11) NOT NULL AUTO_INCREMENT,
        `uuid` VARCHAR(64) DEFAULT NULL COMMENT "uuid",
        `doctor_name` VARCHAR(255) DEFAULT NULL COMMENT "主管医生",
        `patient_guid` VARCHAR(128) DEFAULT NULL COMMENT "编号",
        `patient_name` VARCHAR(128) DEFAULT NULL COMMENT "姓名",
      `inpatient_department` VARCHAR(128) DEFAULT NULL COMMENT "科室",
        `admission_date` datetime DEFAULT NULL COMMENT "入院日期",
        `discharge_date` datetime DEFAULT NULL COMMENT "出院日期",
        `disease_name` VARCHAR(128) DEFAULT NULL COMMENT "诊断名称",
        `indicator_flag` boolean DEFAULT NULL COMMENT "0：肺部病变；1：重症肺炎；3：完成血痰培养；4：完成实验室检查(ESR/CRP/PCT);5:氧合评估正常",
        
        PRIMARY KEY (`id`)      
)  ENGINE = INNODB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8;

插入数据

insert cap_severe_zqc_test(
    indicator_name,
    indicator_count
)
select "严重肺部病变患者", count(distinct uuid) as "数量" from 
(
    select A.uuid, A.examination_item_name from 
    (
        SELECT concat(customer_id, "-", record_id) as uuid, examination_item_name 
        from pt_patient_examination_item as A
        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 "%多侧%")
                        )
    ) as A
    inner JOIN 
    (
        select distinct concat(customer_id, "-", record_id) as uuid from hm_sentry_lx_0625.qc_patient_item where disease_id=128
        UNION 
        select distinct uuid from cap_statistics_full_info_zhaoqingchen
    ) as B on A.uuid = B.uuid  
) as temp