-- ----------------------------------------平台筛查综合统计SQL------------------------------------------------------- -- 做血检的人次统计 SELECT rel11.医联体名称 医联体名称, (IFNULL( 实际血清检测人次, 0 ) + IFNULL( 计划血清检测人次, 0 )) 血清检测总人次, IFNULL( 计划血清检测人次, 0 ) 计划血清检测人次, IFNULL( 实际血清检测人次, 0 ) 实际血清检测人次 FROM ( SELECT 医联体编号, 医联体名称, count( * ) 计划血清检测人次 FROM ( SELECT p.id 患者id, rel2.number 医联体编号, rel2.`name` 医联体名称, rel1.step 计划随访调查进度, CASE rel1.continue_flup WHEN 1 THEN '继续随访' WHEN 0 THEN '停止随访' ELSE '' END 随访状态, rel1.content_type 计划随访内容, rel1.`status` 审核状态 FROM tb_patient p -- 随访状态为继续随访，计划随访内容包含血清检查 RIGHT JOIN ( SELECT patient_id, step, continue_flup, content_type, `status` FROM tb_flup_plan WHERE continue_flup = 1 AND content_type LIKE '%b%' ) rel1 ON rel1.patient_id = p.id LEFT JOIN ( SELECT `name`, number FROM tb_hosp ) rel2 ON rel2.number = p.hosp_number ) rel GROUP BY 医联体名称 ORDER BY 计划血清检测人次 DESC ) rel11 LEFT JOIN ( SELECT 医联体编号, 医联体名称, count( * ) 实际血清检测人次 FROM ( SELECT p.id 患者id, rel1.number 医联体编号, rel1.`name` 医联体名称, rel2.step 实验室检查调查进度, rel2.g17 'G-17', rel2.pg1 'PG-Ⅰ', rel2.pg2 'PG-Ⅱ', CASE rel2.igg WHEN 1 THEN '阳性' WHEN 0 THEN '阴性' ELSE '' END HP抗体（IGG）检测, rel2.`status` 审核状态 FROM tb_patient AS p LEFT JOIN ( SELECT `name`, number FROM tb_hosp ) rel1 ON rel1.number = p.hosp_number LEFT JOIN ( SELECT patient_id, step, g17, pg1, pg2, igg, `status` FROM tb_lab_inspect ) rel2 ON rel2.patient_id = p.id WHERE rel2.`status` NOT IN ( 0, 5 ) ) rel GROUP BY 医联体名称 ) rel22 ON rel11.医联体编号 = rel22.医联体编号 -- 统计每个医联体下计划随访需要做血清检测的人次 SELECT 医联体名称, count( * ) 计划血清检测人次 FROM ( SELECT p.id 患者id, rel2.number 医联体编号, rel2.`name` 医联体名称, rel1.step 计划随访调查进度, CASE rel1.continue_flup WHEN 1 THEN '继续随访' WHEN 0 THEN '停止随访' ELSE '' END 随访状态, rel1.content_type 计划随访内容, rel1.`status` 审核状态 FROM tb_patient p -- 随访状态为继续随访，计划随访内容包含血清检查 RIGHT JOIN (SELECT patient_id, step, continue_flup, content_type, `status` FROM tb_flup_plan WHERE continue_flup = 1 AND content_type LIKE '%b%' ) rel1 ON rel1.patient_id = p.id LEFT JOIN ( SELECT `name`, number FROM tb_hosp ) rel2 ON rel2.number = p.hosp_number ) rel GROUP BY 医联体名称 ORDER BY 计划血清检测人次 DESC -- 实际已做过血清检测的患者人次 SELECT 医联体名称, count( * ) 血清检测人次 FROM ( SELECT p.id 患者id, rel1.number 医联体编号, rel1.`name` 医联体名称, rel2.step 实验室检查调查进度, rel2.g17 'G-17', rel2.pg1 'PG-Ⅰ', rel2.pg2 'PG-Ⅱ', CASE rel2.igg WHEN 1 THEN '阳性' WHEN 0 THEN '阴性' ELSE '' END HP抗体（IGG）检测, rel2.`status` 审核状态 FROM tb_patient AS p LEFT JOIN ( SELECT `name`, number FROM tb_hosp ) rel1 ON rel1.number = p.hosp_number LEFT JOIN ( SELECT patient_id, step, g17, pg1, pg2, igg, `status` FROM tb_lab_inspect ) rel2 ON rel2.patient_id = p.id WHERE rel2.`status` <> 0 and rel2.`status` <> 5 ) rel GROUP BY 医联体名称 ORDER BY 血清检测人次 DESC -- 血检总人次统计 SELECT ( IFNULL( 实际血清检测总人次, 0 ) + IFNULL( 计划血清检测总人次, 0 ) ) 血清检测总人次, IFNULL( rel11.计划血清检测总人次, 0 ) 计划血清检测总人次, IFNULL( rel22.实际血清检测总人次, 0 ) 实际血清检测总人次, IFNULL( rel33.HP抗体（IGG）检测阳性总人次, 0 ) HP抗体（IGG）检测阳性总人次 FROM ( SELECT count( * ) 计划血清检测总人次 FROM ( SELECT p.id 患者id, rel2.number 医联体编号, rel2.`name` 医联体名称, rel1.step 计划随访调查进度, CASE rel1.continue_flup WHEN 1 THEN '继续随访' WHEN 0 THEN '停止随访' ELSE '' END 随访状态, rel1.content_type 计划随访内容, rel1.`status` 审核状态 FROM tb_patient p -- 随访状态为继续随访，计划随访内容包含血清检查 RIGHT JOIN ( SELECT patient_id, step, continue_flup, content_type, `status` FROM tb_flup_plan WHERE continue_flup = 1 AND content_type LIKE '%b%' ) rel1 ON rel1.patient_id = p.id LEFT JOIN ( SELECT `name`, number FROM tb_hosp ) rel2 ON rel2.number = p.hosp_number ) rel ) rel11, ( SELECT count( * ) 实际血清检测总人次 FROM ( SELECT p.id 患者id, rel1.number 医联体编号, rel1.`name` 医联体名称, rel2.step 实验室检查调查进度, rel2.g17 'G-17', rel2.pg1 'PG-Ⅰ', rel2.pg2 'PG-Ⅱ', CASE rel2.igg WHEN 1 THEN '阳性' WHEN 0 THEN '阴性' ELSE '' END HP抗体（IGG）检测, rel2.`status` 审核状态 FROM tb_patient AS p LEFT JOIN ( SELECT `name`, number FROM tb_hosp ) rel1 ON rel1.number = p.hosp_number LEFT JOIN ( SELECT patient_id, step, g17, pg1, pg2, igg, `status` FROM tb_lab_inspect ) rel2 ON rel2.patient_id = p.id WHERE rel2.`status` NOT IN ( 0, 5 ) ) rel ) rel22, ( SELECT count( * ) HP抗体（IGG）检测阳性总人次 FROM ( SELECT p.id 患者id, rel1.number 医联体编号, rel1.`name` 医联体名称, rel2.step 实验室检查调查进度, rel2.g17 'G-17', rel2.pg1 'PG-Ⅰ', rel2.pg2 'PG-Ⅱ', CASE rel2.igg WHEN 1 THEN '阳性' WHEN 0 THEN '阴性' ELSE '' END HP抗体（IGG）检测, rel2.`status` 审核状态 FROM tb_patient AS p LEFT JOIN ( SELECT `name`, number FROM tb_hosp ) rel1 ON rel1.number = p.hosp_number RIGHT JOIN ( SELECT patient_id, step, g17, pg1, pg2, igg, `status` FROM tb_lab_inspect WHERE `status` NOT IN ( 0, 5 ) ) rel2 ON rel2.patient_id = p.id ) rel WHERE rel.HP抗体（IGG）检测 = '阳性' ) rel33 -- HP抗体（IGG）检测阳性人次 SELECT count( * ) HP抗体（IGG）检测阳性人次 FROM ( SELECT p.id 患者id, rel1.number 医联体编号, rel1.`name` 医联体名称, rel2.step 实验室检查调查进度, rel2.g17 'G-17', rel2.pg1 'PG-Ⅰ', rel2.pg2 'PG-Ⅱ', CASE rel2.igg WHEN 1 THEN '阳性' WHEN 0 THEN '阴性' ELSE '' END HP抗体（IGG）检测, rel2.`status` 审核状态 FROM tb_patient AS p LEFT JOIN ( SELECT `name`, number FROM tb_hosp ) rel1 ON rel1.number = p.hosp_number RIGHT JOIN ( SELECT patient_id, step, g17, pg1, pg2, igg, `status` FROM tb_lab_inspect where `status` NOT IN ( 0, 5 )) rel2 ON rel2.patient_id = p.id ) rel WHERE rel.HP抗体（IGG）检测 = '阳性' -- 胃癌患病风险等级评估统计 SELECT 胃癌患病风险等级, count( * ) 患者总人次 FROM ( SELECT p.id 患者id, rel1.score 胃癌患病风险评分, CASE rel1.grade WHEN 1 THEN '低风险' WHEN 2 THEN '中风险' WHEN 3 THEN '高风险' ELSE '' END 胃癌患病风险等级 FROM tb_patient p RIGHT JOIN ( SELECT patient_id, score, grade FROM tb_risk_assess ) rel1 ON rel1.patient_id = p.id ) rel GROUP BY 胃癌患病风险等级 -- 胃镜检查人次统计 SELECT ( IFNULL( 实际胃镜检查总人次, 0 ) + IFNULL( 计划胃镜检查总人次, 0 ) ) 胃镜检查总人次, IFNULL( rel11.计划胃镜检查总人次, 0 ) 计划胃镜检查总人次, IFNULL( rel22.实际胃镜检查总人次, 0 ) 实际胃镜检查总人次, IFNULL( rel22.胃镜精查总人次, 0 ) 胃镜精查总人次 FROM ( SELECT count( * ) 计划胃镜检查总人次 FROM ( SELECT p.id 患者id, rel1.step 计划随访调查进度, CASE rel1.continue_flup WHEN 1 THEN '继续随访' WHEN 0 THEN '停止随访' ELSE '' END 随访状态, rel1.content_type 随访内容 FROM tb_patient p RIGHT JOIN ( SELECT patient_id, step, continue_flup, content_type FROM tb_flup_plan WHERE continue_flup = 1 AND content_type LIKE '%a%' ) rel1 ON rel1.patient_id = p.id ) rel ) rel11, ( SELECT count( * ) 实际胃镜检查总人次, count( 胃镜精查方式 <> '[]' OR NULL ) 胃镜精查总人次 FROM ( SELECT p.id 患者id, rel1.step 胃镜检查调查进度, CASE rel1.is_inspect WHEN 1 THEN '是' WHEN 0 THEN '否' ELSE '' END 是否胃镜检查, rel1.inspect_way 胃镜检查方式, CASE rel1.capsule WHEN 1 THEN '异常' WHEN 2 THEN '未见异常' ELSE '' END 胃镜检查结果, rel1.precise_way 胃镜精查方式, CASE WHEN rel1.diagnose_result = 1 THEN '异常' WHEN rel1.diagnose_result = 2 THEN '未见明显异常' ELSE '' END 最终诊断结果, rel1.diagnose_info 最终诊断详情, rel1.STATUS 审核状态 FROM tb_patient p RIGHT JOIN ( SELECT patient_id, step, inspect_way, precise_way, capsule, diagnose_result, diagnose_info, is_inspect, `status` FROM tb_gastro_inspect WHERE is_inspect = 1 AND `status` NOT IN ( 0, 5 ) ) rel1 ON rel1.patient_id = p.id ) rel ) rel22 -- 随访计划中需要做胃镜检查的患者人次统计 SELECT p.id 患者id, rel1.step 计划随访调查进度, CASE rel1.continue_flup WHEN 1 THEN '继续随访' WHEN 0 THEN '停止随访' ELSE '' END 随访状态, rel1.content_type 随访内容 FROM tb_patient p -- 随访状态为继续随访，计划随访内容包含胃镜检查 RIGHT JOIN ( SELECT patient_id, step, continue_flup, content_type FROM tb_flup_plan WHERE continue_flup = 1 AND content_type LIKE '%a%' ) rel1 ON rel1.patient_id = p.id -- 实际已做过胃镜检查的患者人次 SELECT p.id 患者id, rel1.step 胃镜检查调查进度, CASE rel1.is_inspect WHEN 1 THEN '是' WHEN 0 THEN '否' ELSE '' END 是否胃镜检查, rel1.inspect_way 胃镜检查方式, CASE rel1.capsule WHEN 1 THEN '异常' WHEN 2 THEN '未见异常' ELSE '' END 胃镜检查结果, rel1.precise_way 胃镜精查方式, CASE WHEN rel1.diagnose_result = 1 THEN '异常' WHEN rel1.diagnose_result = 2 THEN '未见明显异常' ELSE '' END 最终诊断结果, rel1.diagnose_info 最终诊断详情, rel1.STATUS 审核状态 FROM tb_patient p RIGHT JOIN ( SELECT patient_id, step, inspect_way, precise_way, capsule, diagnose_result, diagnose_info, is_inspect, `status` FROM tb_gastro_inspect WHERE -- 是否进行胃镜检查 is_inspect = 1 -- 状态不能是待录入（暂存）0-待录入；5-待提交 AND `status` NOT IN ( 0, 5 ) ) rel1 ON rel1.patient_id = p.id -- 实际胃镜精查人次 SELECT p.id 患者id, rel1.step 胃镜检查调查进度, CASE rel1.is_inspect WHEN 1 THEN '是' WHEN 0 THEN '否' ELSE '' END 是否胃镜检查, rel1.inspect_way 胃镜检查方式, CASE rel1.capsule WHEN 1 THEN '异常' WHEN 2 THEN '未见异常' ELSE '' END 胃镜检查结果, rel1.precise_way 胃镜精查方式, CASE WHEN rel1.diagnose_result = 1 THEN '异常' WHEN rel1.diagnose_result = 2 THEN '未见明显异常' ELSE '' END 最终诊断结果, rel1.diagnose_info 最终诊断详情, rel1.STATUS 审核状态 FROM tb_patient p RIGHT JOIN ( SELECT patient_id, step, is_inspect, inspect_way, capsule, precise_way, diagnose_result, diagnose_info, `status` FROM tb_gastro_inspect WHERE-- 是否进行胃镜检查 is_inspect = 1 -- 状态不能是待录入（暂存）0-待录入；5-待提交 AND `status` NOT IN ( 0, 5 ) AND precise_way IS NOT NULL AND precise_way <> '[]' ) rel1 ON rel1.patient_id = p.id -- 胃镜精查异常人次 SELECT p.id patientId, rel1.step gastroStep, rel1.is_inspect isGastro, rel1.inspect_way gastroWay, rel1.capsule gastroResult, rel1.precise_way gastroDetailWay, rel1.diagnose_result gastroDetailResult, rel1.diagnose_info gastroDetailInfo, rel1.`status` auditStatus FROM tb_patient p RIGHT JOIN ( SELECT patient_id, step, is_inspect, inspect_way, capsule, precise_way, diagnose_result, diagnose_info, `status` FROM tb_gastro_inspect WHERE -- 是否进行胃镜检查 is_inspect = 1 -- 状态不能是待录入（暂存）0-待录入；5-待提交 AND `status` NOT IN ( 0, 5 ) -- 胃镜精查异常 and diagnose_result = 1 ) rel1 ON rel1.patient_id = p.id -- "a":1 食管粘膜上皮内瘤变 -- "b":1 早期食管癌 -- "c":1 进展期食管癌 -- "d":1 Barret食管 -- "e":1 反流性食管炎 -- "f":1 胃粘膜上皮内瘤变 -- "g":1 早期胃癌 -- "h":1 进展期胃癌 -- "i":1 萎缩性胃炎 -- "j":1 胃溃疡 -- "k":1 胃息肉 -- "l":1 非萎缩性胃炎 -- "m":1 十二指肠球部溃疡 -- "n":1 其它病变 -- 胃镜检查诊断结果统计 SELECT count( gastroDetailInfo LIKE '%"a":1%' OR NULL ) AS 食管粘膜上皮内瘤变, count( gastroDetailInfo LIKE '%"b":1%' OR NULL ) AS 早期食管癌, count( gastroDetailInfo LIKE '%"c":1%' OR NULL ) AS 进展期食管癌, count( gastroDetailInfo LIKE '%"d":1%' OR NULL ) AS Barret食管, count( gastroDetailInfo LIKE '%"e":1%' OR NULL ) AS 反流性食管炎, count( gastroDetailInfo LIKE '%"f":1%' OR NULL ) AS 胃粘膜上皮内瘤变, count( gastroDetailInfo LIKE '%"g":1%' OR NULL ) AS 早期胃癌, count( gastroDetailInfo LIKE '%"h":1%' OR NULL ) AS 进展期胃癌, count( gastroDetailInfo LIKE '%"i":1%' OR NULL ) AS 萎缩性胃炎, count( gastroDetailInfo LIKE '%"j":1%' OR NULL ) AS 胃溃疡, count( gastroDetailInfo LIKE '%"k":1%' OR NULL ) AS 胃息肉, count( gastroDetailInfo LIKE '%"l":1%' OR NULL ) AS 非萎缩性胃炎, count( gastroDetailInfo LIKE '%"m":1%' OR NULL ) AS 十二指肠球部溃疡, count( gastroDetailInfo LIKE '%"n":%' OR NULL ) AS 其它病变 FROM ( SELECT p.id patientId, rel1.step gastroStep, rel1.is_inspect isGastro, rel1.inspect_way gastroWay, rel1.capsule gastroResult, rel1.precise_way gastroDetailWay, rel1.diagnose_result gastroDetailResult, rel1.diagnose_info gastroDetailInfo, rel1.`status` auditStatus FROM tb_patient p RIGHT JOIN ( SELECT patient_id, step, is_inspect, inspect_way, capsule, precise_way, diagnose_result, diagnose_info, `status` FROM tb_gastro_inspect WHERE is_inspect = 1 AND `status` NOT IN ( 0, 5 ) AND diagnose_result = 1 ) rel1 ON rel1.patient_id = p.id ) rel -- 各医联体胃镜检查诊断结果统计 SELECT hospName 医联体名称, ( count( gastroDetailInfo LIKE '%"a":1%' OR NULL ) + count( gastroDetailInfo LIKE '%"b":1%' OR NULL ) + count( gastroDetailInfo LIKE '%"c":1%' OR NULL ) + count( gastroDetailInfo LIKE '%"d":1%' OR NULL ) + count( gastroDetailInfo LIKE '%"e":1%' OR NULL ) + count( gastroDetailInfo LIKE '%"f":1%' OR NULL ) + count( gastroDetailInfo LIKE '%"g":1%' OR NULL ) + count( gastroDetailInfo LIKE '%"h":1%' OR NULL ) + count( gastroDetailInfo LIKE '%"i":1%' OR NULL ) + count( gastroDetailInfo LIKE '%"j":1%' OR NULL ) + count( gastroDetailInfo LIKE '%"k":1%' OR NULL ) + count( gastroDetailInfo LIKE '%"l":1%' OR NULL ) + count( gastroDetailInfo LIKE '%"m":1%' OR NULL ) + count( gastroDetailInfo LIKE '%"n":%' OR NULL ) ) 合计, count( gastroDetailInfo LIKE '%"a":1%' OR NULL ) AS 食管粘膜上皮内瘤变, count( gastroDetailInfo LIKE '%"b":1%' OR NULL ) AS 早期食管癌, count( gastroDetailInfo LIKE '%"c":1%' OR NULL ) AS 进展期食管癌, count( gastroDetailInfo LIKE '%"d":1%' OR NULL ) AS Barret食管, count( gastroDetailInfo LIKE '%"e":1%' OR NULL ) AS 反流性食管炎, count( gastroDetailInfo LIKE '%"f":1%' OR NULL ) AS 胃粘膜上皮内瘤变, count( gastroDetailInfo LIKE '%"g":1%' OR NULL ) AS 早期胃癌, count( gastroDetailInfo LIKE '%"h":1%' OR NULL ) AS 进展期胃癌, count( gastroDetailInfo LIKE '%"i":1%' OR NULL ) AS 萎缩性胃炎, count( gastroDetailInfo LIKE '%"j":1%' OR NULL ) AS 胃溃疡, count( gastroDetailInfo LIKE '%"k":1%' OR NULL ) AS 胃息肉, count( gastroDetailInfo LIKE '%"l":1%' OR NULL ) AS 非萎缩性胃炎, count( gastroDetailInfo LIKE '%"m":1%' OR NULL ) AS 十二指肠球部溃疡, count( gastroDetailInfo LIKE '%"n":%' OR NULL ) AS 其它病变 FROM ( SELECT p.id patientId, rel2.number hospNumber, rel2.`name` hospName, rel1.step gastroStep, rel1.is_inspect isGastro, rel1.inspect_way gastroWay, rel1.capsule gastroResult, rel1.precise_way gastroDetailWay, rel1.diagnose_result gastroDetailResult, rel1.diagnose_info gastroDetailInfo, rel1.`status` auditStatus FROM tb_patient p RIGHT JOIN ( SELECT patient_id, step, is_inspect, inspect_way, capsule, precise_way, diagnose_result, diagnose_info, `status` FROM tb_gastro_inspect WHERE is_inspect = 1 AND `status` NOT IN ( 0, 5 ) AND diagnose_result = 1 ) rel1 ON rel1.patient_id = p.id INNER JOIN ( SELECT `name`, number FROM tb_hosp ) rel2 ON rel2.number = p.hosp_number ) rel GROUP BY hospName ORDER BY 早期胃癌 DESC -- 高级别上皮内瘤变：高级别食管粘膜上皮内瘤变（"type_a":"a"）+高级别胃粘膜上皮内瘤变（"type_f":"a"）是否等于 高级别上皮内瘤变 人次？ SELECT count( gastroDetailInfo LIKE '%"type_a":"a"%' OR gastroDetailInfo LIKE '%"type_f":"a"%' OR NULL ) AS 高级别上皮内瘤变, count( gastroDetailInfo LIKE '%"type_a":"a"%' OR NULL ) AS 高级别食管粘膜上皮内瘤变, count( gastroDetailInfo LIKE '%"type_f":"a"%' OR NULL ) AS 高级别胃粘膜上皮内瘤变 FROM ( SELECT p.id patientId, rel1.step gastroStep, rel1.is_inspect isGastro, rel1.inspect_way gastroWay, rel1.capsule gastroResult, rel1.precise_way gastroDetailWay, rel1.diagnose_result gastroDetailResult, rel1.diagnose_info gastroDetailInfo, rel1.`status` auditStatus FROM tb_patient p RIGHT JOIN ( SELECT patient_id, step, is_inspect, inspect_way, capsule, precise_way, diagnose_result, diagnose_info, `status` FROM tb_gastro_inspect WHERE is_inspect = 1 AND `status` NOT IN ( 0, 5 ) AND diagnose_result = 1 ) rel1 ON rel1.patient_id = p.id ) rel -- 页面筛查数据统计结果--过程 SELECT p.id 患者id, p.`name` 患者姓名, rel5.number 医联体编号, rel5.`name` 医联体名称, CASE rel1.igg WHEN 1 THEN '阳性' WHEN 0 THEN '阴性' ELSE '' END HP抗体（IGG）检测, CASE rel2.grade WHEN 1 THEN '低风险' WHEN 2 THEN '中风险' WHEN 3 THEN '高风险' ELSE '' END 胃癌患病风险等级, CASE rel3.is_inspect WHEN 1 THEN '是' WHEN 0 THEN '否' ELSE '否' END 是否胃镜检查, rel3.inspect_way 胃镜检查方式, CASE rel3.capsule WHEN 1 THEN '异常' WHEN 2 THEN '未见异常' ELSE '' END 胃镜检查结果, rel3.precise_way 胃镜精查方式, CASE WHEN rel3.diagnose_result = 1 THEN '异常' WHEN rel3.diagnose_result = 2 THEN '未见明显异常' ELSE '' END 最终诊断结果, rel3.diagnose_info 最终诊断详情, CASE rel4.continue_flup WHEN 1 THEN '继续随访' WHEN 0 THEN '停止随访' ELSE '' END 是否继续随访, rel4.content_type 继续随访内容, rel1.`status` 筛查状态 FROM tb_patient p RIGHT JOIN ( SELECT patient_id, igg, step, `status` FROM tb_lab_inspect WHERE `status` NOT IN ( 0, 5 ) ) rel1 ON rel1.patient_id = p.id LEFT JOIN ( SELECT patient_id, step, score, grade, `status` FROM tb_risk_assess WHERE `status` NOT IN ( 0, 5 ) ) rel2 ON rel2.patient_id = p.id AND rel2.step = rel1.step AND rel2.`status` = rel1.`status` LEFT JOIN ( SELECT patient_id, step, is_inspect, inspect_way, capsule, precise_way, diagnose_result, diagnose_info, `status` FROM tb_gastro_inspect WHERE `status` NOT IN ( 0, 5 ) ) rel3 ON rel3.patient_id = p.id AND rel3.step = rel2.step AND rel3.`status` = rel2.`status` LEFT JOIN ( SELECT patient_id, step, continue_flup, content_type, `status` FROM tb_flup_plan WHERE `status` NOT IN ( 0, 5 ) ) rel4 ON rel4.patient_id = p.id AND rel4.step = rel3.step AND rel4.`status` = rel3.`status` LEFT JOIN ( SELECT number, `name` FROM tb_hosp ) rel5 ON rel5.number = p.hosp_number -- 页面筛查数据统计结果 SELECT rel.医联体编号 医联体编号, rel.医联体名称 医联体名称, count( 筛查状态 = 1 OR NULL ) 筛查申请数, count( * ) 筛查总数 FROM ( SELECT p.id 患者id, p.`name` 患者姓名, rel5.number 医联体编号, rel5.`name` 医联体名称, CASE rel1.igg WHEN 1 THEN '阳性' WHEN 0 THEN '阴性' ELSE '' END HP抗体（IGG）检测, CASE rel2.grade WHEN 1 THEN '低风险' WHEN 2 THEN '中风险' WHEN 3 THEN '高风险' ELSE '' END 胃癌患病风险等级, CASE rel3.is_inspect WHEN 1 THEN '是' WHEN 0 THEN '否' ELSE '否' END 是否胃镜检查, rel3.inspect_way 胃镜检查方式, CASE rel3.capsule WHEN 1 THEN '异常' WHEN 2 THEN '未见异常' ELSE '' END 胃镜检查结果, rel3.precise_way 胃镜精查方式, CASE rel3.diagnose_result WHEN 1 THEN '异常' WHEN 2 THEN '未见明显异常' ELSE '' END 最终诊断结果, rel3.diagnose_info 最终诊断详情, CASE rel4.continue_flup WHEN 1 THEN '继续随访' WHEN 0 THEN '停止随访' ELSE '' END 是否继续随访, rel4.content_type 继续随访内容, rel1.`status` 筛查状态 FROM tb_patient p RIGHT JOIN ( SELECT patient_id, igg, step, `status` FROM tb_lab_inspect WHERE `status` NOT IN ( 0, 5 ) ) rel1 ON rel1.patient_id = p.id LEFT JOIN ( SELECT patient_id, step, score, grade, `status` FROM tb_risk_assess WHERE `status` NOT IN ( 0, 5 ) ) rel2 ON rel2.patient_id = p.id AND rel2.step = rel1.step AND rel2.`status` = rel1.`status` LEFT JOIN ( SELECT patient_id, step, is_inspect, inspect_way, capsule, precise_way, diagnose_result, diagnose_info, `status` FROM tb_gastro_inspect WHERE `status` NOT IN ( 0, 5 ) ) rel3 ON rel3.patient_id = p.id AND rel3.step = rel2.step AND rel3.`status` = rel2.`status` LEFT JOIN ( SELECT patient_id, step, continue_flup, content_type, `status` FROM tb_flup_plan WHERE `status` NOT IN ( 0, 5 ) ) rel4 ON rel4.patient_id = p.id AND rel4.step = rel3.step AND rel4.`status` = rel3.`status` LEFT JOIN ( SELECT number, `name` FROM tb_hosp ) rel5 ON rel5.number = p.hosp_number ) rel GROUP BY 医联体名称 ORDER BY 筛查总数 DESC -- 页面血检数据统计 SELECT 医联体编号, 医联体名称, sum( 计划血清检测总人次 + 实际血清检测总人次 ) 血清检测总人次, sum( 计划血清检测总人次 ) 计划血清检测总人次, sum( 实际血清检测总人次 ) 实际血清检测总人次, sum( HP抗体（IGG）检测阳性总人次 ) HP抗体（IGG）检测阳性总人次, sum( 早癌患者数 ) 早癌患者数 FROM ( ( SELECT 医联体编号, 医联体名称, count( * ) 计划血清检测总人次, count( NULL ) 实际血清检测总人次, count( NULL ) HP抗体（IGG）检测阳性总人次, count( NULL ) 早癌患者数 FROM ( SELECT p.id 患者id, rel2.number 医联体编号, rel2.`name` 医联体名称, rel1.step 计划随访调查进度, CASE rel1.continue_flup WHEN 1 THEN '继续随访' WHEN 0 THEN '停止随访' ELSE '' END 随访状态, rel1.content_type 计划随访内容, rel1.`status` 审核状态 FROM tb_patient p -- 随访状态为继续随访，计划随访内容包含血清检查 RIGHT JOIN ( SELECT patient_id, step, continue_flup, content_type, `status` FROM tb_flup_plan WHERE continue_flup = 1 AND content_type LIKE '%b%' ) rel1 ON rel1.patient_id = p.id LEFT JOIN ( SELECT `name`, number FROM tb_hosp ) rel2 ON rel2.number = p.hosp_number ) rel GROUP BY 医联体名称 ) UNION ( SELECT 医联体编号, 医联体名称, count( NULL ) 计划血清检测总人次, count( * ) 实际血清检测总人次, count( HP抗体（IGG）检测 = '阳性' OR NULL ) HP抗体（IGG）检测阳性总人次, count( NULL ) 早癌患者数 FROM ( SELECT p.id 患者id, rel1.number 医联体编号, rel1.`name` 医联体名称, rel2.step 实验室检查调查进度, rel2.g17 'G-17', rel2.pg1 'PG-Ⅰ', rel2.pg2 'PG-Ⅱ', CASE rel2.igg WHEN 1 THEN '阳性' WHEN 0 THEN '阴性' ELSE '' END HP抗体（IGG）检测, rel2.`status` 审核状态 FROM tb_patient AS p LEFT JOIN ( SELECT `name`, number FROM tb_hosp ) rel1 ON rel1.number = p.hosp_number RIGHT JOIN ( SELECT patient_id, step, g17, pg1, pg2, igg, `status` FROM tb_lab_inspect ) rel2 ON rel2.patient_id = p.id WHERE rel2.`status` NOT IN ( 0, 5 ) ) rel GROUP BY 医联体名称 ) UNION ( SELECT hospNumber 医联体编号, hospName 医联体名称, count( NULL ) 计划血清检测总人次, count( NULL ) 实际血清检测总人次, count( NULL ) HP抗体（IGG）检测阳性总人次, count( gastroDetailInfo LIKE '%"b":1%' OR NULL ) + count( gastroDetailInfo LIKE '%"g":1%' OR NULL ) 早癌患者数 FROM ( SELECT p.id patientId, rel2.number hospNumber, rel2.`name` hospName, rel1.step gastroStep, rel1.is_inspect isGastro, rel1.inspect_way gastroWay, rel1.capsule gastroResult, rel1.precise_way gastroDetailWay, rel1.diagnose_result gastroDetailResult, rel1.diagnose_info gastroDetailInfo, rel1.`status` auditStatus FROM tb_patient p RIGHT JOIN ( SELECT patient_id, step, is_inspect, inspect_way, capsule, precise_way, diagnose_result, diagnose_info, `status` FROM tb_gastro_inspect WHERE is_inspect = 1 AND `status` NOT IN ( 0, 5 ) AND diagnose_result = 1 ) rel1 ON rel1.patient_id = p.id LEFT JOIN ( SELECT `name`, number FROM tb_hosp ) rel2 ON rel2.number = p.hosp_number ) rel GROUP BY 医联体名称 ) ) rel GROUP BY 医联体名称 SELECT hospNumber 医联体编号, hospName 医联体名称, count( gastroDetailInfo LIKE '%"b":1%' OR NULL ) + count( gastroDetailInfo LIKE '%"g":1%' OR NULL ) 早癌患者数, count( gastroDetailInfo LIKE '%"b":1%' OR NULL ) AS 早期食管癌, count( gastroDetailInfo LIKE '%"g":1%' OR NULL ) AS 早期胃癌 FROM ( SELECT p.id patientId, rel2.number hospNumber, rel2.`name` hospName, rel1.step gastroStep, rel1.is_inspect isGastro, rel1.inspect_way gastroWay, rel1.capsule gastroResult, rel1.precise_way gastroDetailWay, rel1.diagnose_result gastroDetailResult, rel1.diagnose_info gastroDetailInfo, rel1.`status` auditStatus FROM tb_patient p RIGHT JOIN ( SELECT patient_id, step, is_inspect, inspect_way, capsule, precise_way, diagnose_result, diagnose_info, `status` FROM tb_gastro_inspect WHERE is_inspect = 1 AND `status` NOT IN ( 0, 5 ) AND diagnose_result = 1 ) rel1 ON rel1.patient_id = p.id LEFT JOIN ( SELECT `name`, number FROM tb_hosp ) rel2 ON rel2.number = p.hosp_number ) rel GROUP BY 医联体名称 -- 页面胃镜检查数据统计 SELECT 医联体编号, 医联体名称, sum( 计划胃镜检查总人次 + 实际胃镜检查总人次 ) 胃镜检查总人次, sum( 计划胃镜检查总人次 ) 计划胃镜检查总人次, sum( 实际胃镜检查总人次 ) 实际胃镜检查总人次, sum( 胃镜精查总人次 ) 胃镜精查总人次, sum( 早癌患者数 ) 早癌患者数 FROM ( ( SELECT 医联体编号, 医联体名称, count( * ) 计划胃镜检查总人次, count( NULL ) 实际胃镜检查总人次, count( NULL ) 胃镜精查总人次, count( NULL ) 早癌患者数 FROM ( SELECT p.id 患者id, rel2.number 医联体编号, rel2.`name` 医联体名称, rel1.step 计划随访调查进度, CASE rel1.continue_flup WHEN 1 THEN '继续随访' WHEN 0 THEN '停止随访' ELSE '' END 随访状态, rel1.content_type 随访内容 FROM tb_patient p RIGHT JOIN ( SELECT patient_id, step, continue_flup, content_type FROM tb_flup_plan WHERE continue_flup = 1 AND content_type LIKE '%a%' ) rel1 ON rel1.patient_id = p.id INNER JOIN ( SELECT `name`, number FROM tb_hosp ) rel2 ON rel2.number = p.hosp_number ) rel GROUP BY 医联体名称 ) UNION ( SELECT 医联体编号, 医联体名称, count( NULL ) 计划胃镜检查总人次, count( * ) 实际胃镜检查总人次, count( 胃镜精查方式 <> '[]' OR NULL ) 胃镜精查总人次, count( 最终诊断详情 LIKE '%"b":1%' OR NULL ) + count( 最终诊断详情 LIKE '%"g":1%' OR NULL ) 早癌患者数 FROM ( SELECT p.id 患者id, rel2.number 医联体编号, rel2.`name` 医联体名称, rel1.step 胃镜检查调查进度, CASE rel1.is_inspect WHEN 1 THEN '是' WHEN 0 THEN '否' ELSE '' END 是否胃镜检查, rel1.inspect_way 胃镜检查方式, CASE rel1.capsule WHEN 1 THEN '异常' WHEN 2 THEN '未见异常' ELSE '' END 胃镜检查结果, rel1.precise_way 胃镜精查方式, CASE WHEN rel1.diagnose_result = 1 THEN '异常' WHEN rel1.diagnose_result = 2 THEN '未见明显异常' ELSE '' END 最终诊断结果, rel1.diagnose_info 最终诊断详情, rel1.STATUS 审核状态 FROM tb_patient p RIGHT JOIN ( SELECT patient_id, step, inspect_way, precise_way, capsule, diagnose_result, diagnose_info, is_inspect, `status` FROM tb_gastro_inspect WHERE is_inspect = 1 AND `status` NOT IN ( 0, 5 ) ) rel1 ON rel1.patient_id = p.id INNER JOIN ( SELECT `name`, number FROM tb_hosp ) rel2 ON rel2.number = p.hosp_number ) rel GROUP BY 医联体名称 ) ) rel GROUP BY 医联体名称 -- 页面胃镜检查地区统计（整理过程） SELECT 省份, 医联体编号, 医联体名称, sum( 计划胃镜检查总人次 + 实际胃镜检查总人次 ) 胃镜检查总人次, sum( 计划胃镜检查总人次 ) 计划胃镜检查总人次, sum( 实际胃镜检查总人次 ) 实际胃镜检查总人次, sum( 胃镜精查总人次 ) 胃镜精查总人次, sum( 早癌患者数 ) 早癌患者数 FROM ( ( SELECT 省份, 医联体编号, 医联体名称, count( * ) 计划胃镜检查总人次, count( NULL ) 实际胃镜检查总人次, count( NULL ) 胃镜精查总人次, count( NULL ) 早癌患者数 FROM ( SELECT p.id 患者id, rel3.area 省份, rel2.number 医联体编号, rel2.`name` 医联体名称, rel1.step 计划随访调查进度, CASE rel1.continue_flup WHEN 1 THEN '继续随访' WHEN 0 THEN '停止随访' ELSE '' END 随访状态, rel1.content_type 随访内容 FROM tb_patient p RIGHT JOIN ( SELECT patient_id, step, continue_flup, content_type FROM tb_flup_plan WHERE continue_flup = 1 AND content_type LIKE '%a%' ) rel1 ON rel1.patient_id = p.id inner JOIN ( SELECT `name`, number FROM tb_hosp ) rel2 ON rel2.number = p.hosp_number left join ( select area, `name` from tmp_tb_hosp_area ) rel3 on rel3.`name` = rel2.`name` ) rel GROUP BY -- 省份 医联体名称 ) UNION ( SELECT 省份, 医联体编号, 医联体名称, count( NULL ) 计划胃镜检查总人次, count( * ) 实际胃镜检查总人次, count( 胃镜精查方式 <> '[]' OR NULL ) 胃镜精查总人次, count( 最终诊断详情 LIKE '%"b":1%' OR NULL ) + count( 最终诊断详情 LIKE '%"g":1%' OR NULL ) 早癌患者数 FROM ( SELECT p.id 患者id, rel3.area 省份, rel2.number 医联体编号, rel2.`name` 医联体名称, rel1.step 胃镜检查调查进度, CASE rel1.is_inspect WHEN 1 THEN '是' WHEN 0 THEN '否' ELSE '' END 是否胃镜检查, rel1.inspect_way 胃镜检查方式, CASE rel1.capsule WHEN 1 THEN '异常' WHEN 2 THEN '未见异常' ELSE '' END 胃镜检查结果, rel1.precise_way 胃镜精查方式, CASE WHEN rel1.diagnose_result = 1 THEN '异常' WHEN rel1.diagnose_result = 2 THEN '未见明显异常' ELSE '' END 最终诊断结果, rel1.diagnose_info 最终诊断详情, rel1.STATUS 审核状态 FROM tb_patient p RIGHT JOIN ( SELECT patient_id, step, inspect_way, precise_way, capsule, diagnose_result, diagnose_info, is_inspect, `status` FROM tb_gastro_inspect WHERE is_inspect = 1 AND `status` NOT IN ( 0, 5 ) ) rel1 ON rel1.patient_id = p.id inner JOIN ( SELECT `name`, number FROM tb_hosp ) rel2 ON rel2.number = p.hosp_number left join ( select area, `name` from tmp_tb_hosp_area ) rel3 on rel3.`name` = rel2.`name` ) rel GROUP BY -- 省份 医联体名称 ) ) rel GROUP BY -- 省份 医联体名称 -- 页面胃镜检查地区统计（结果） SELECT 省份, sum( 计划胃镜检查总人次 + 实际胃镜检查总人次 ) 胃镜检查总人次, sum( 计划胃镜检查总人次 ) 计划胃镜检查总人次, sum( 实际胃镜检查总人次 ) 实际胃镜检查总人次, sum( 胃镜精查总人次 ) 胃镜精查总人次, sum( 早癌患者数 ) 早癌患者数 FROM ( ( SELECT 省份, count( * ) 计划胃镜检查总人次, count( NULL ) 实际胃镜检查总人次, count( NULL ) 胃镜精查总人次, count( NULL ) 早癌患者数 FROM ( SELECT p.id 患者id, rel3.area 省份, rel1.step 计划随访调查进度, CASE rel1.continue_flup WHEN 1 THEN '继续随访' WHEN 0 THEN '停止随访' ELSE '' END 随访状态, rel1.content_type 随访内容 FROM tb_patient p RIGHT JOIN ( SELECT patient_id, step, continue_flup, content_type FROM tb_flup_plan WHERE continue_flup = 1 AND content_type LIKE '%a%' ) rel1 ON rel1.patient_id = p.id inner JOIN ( SELECT `name`, number FROM tb_hosp ) rel2 ON rel2.number = p.hosp_number left join ( select area, `name` from tmp_tb_hosp_area ) rel3 on rel3.`name` = rel2.`name` ) rel GROUP BY 省份 ) UNION ( SELECT 省份, count( NULL ) 计划胃镜检查总人次, count( * ) 实际胃镜检查总人次, count( 胃镜精查方式 <> '[]' OR NULL ) 胃镜精查总人次, count( 最终诊断详情 LIKE '%"b":1%' OR NULL ) + count( 最终诊断详情 LIKE '%"g":1%' OR NULL ) 早癌患者数 FROM ( SELECT p.id 患者id, rel3.area 省份, rel1.step 胃镜检查调查进度, CASE rel1.is_inspect WHEN 1 THEN '是' WHEN 0 THEN '否' ELSE '' END 是否胃镜检查, rel1.inspect_way 胃镜检查方式, CASE rel1.capsule WHEN 1 THEN '异常' WHEN 2 THEN '未见异常' ELSE '' END 胃镜检查结果, rel1.precise_way 胃镜精查方式, CASE WHEN rel1.diagnose_result = 1 THEN '异常' WHEN rel1.diagnose_result = 2 THEN '未见明显异常' ELSE '' END 最终诊断结果, rel1.diagnose_info 最终诊断详情, rel1.STATUS 审核状态 FROM tb_patient p RIGHT JOIN ( SELECT patient_id, step, inspect_way, precise_way, capsule, diagnose_result, diagnose_info, is_inspect, `status` FROM tb_gastro_inspect WHERE is_inspect = 1 AND `status` NOT IN ( 0, 5 ) ) rel1 ON rel1.patient_id = p.id inner JOIN ( SELECT `name`, number FROM tb_hosp ) rel2 ON rel2.number = p.hosp_number left join ( select area, `name` from tmp_tb_hosp_area ) rel3 on rel3.`name` = rel2.`name` ) rel GROUP BY 省份 ) ) rel GROUP BY 省份 SELECT provinceName, sum( provinceGastroscopyPlan + provinceGastroscopyActual ) provinceGastroscopyTotal, sum( provinceGastroscopyPlan ) provinceGastroscopyPlan, sum( provinceGastroscopyActual ) provinceGastroscopyActual, sum( provinceGastroscopyDetail ) provinceGastroscopyDetail, sum( provinceEarlyCancer ) provinceEarlyCancer FROM ( ( SELECT provinceName, count( * ) provinceGastroscopyPlan, count( NULL ) provinceGastroscopyActual, count( NULL ) provinceGastroscopyDetail, count( NULL ) provinceEarlyCancer FROM ( SELECT p.id patientId, rel3.area provinceName, rel1.step planFlupStep, CASE rel1.continue_flup WHEN 1 THEN '继续随访' WHEN 0 THEN '停止随访' ELSE '' END isContinueFlup, rel1.content_type planFlupContent FROM tb_patient p RIGHT JOIN ( SELECT patient_id, step, continue_flup, content_type FROM tb_flup_plan WHERE continue_flup = 1 AND content_type LIKE '%a%' ) rel1 ON rel1.patient_id = p.id INNER JOIN ( SELECT `name`, number FROM tb_hosp ) rel2 ON rel2.number = p.hosp_number LEFT JOIN ( SELECT area, `name` FROM tmp_tb_hosp_area ) rel3 ON rel3.`name` = rel2.`name` ) rel GROUP BY provinceName ) UNION ( SELECT provinceName, count( NULL ) provinceGastroscopyPlan, count( * ) provinceGastroscopyActual, count( gastroscopyDetailWay <> '[]' OR NULL ) provinceGastroscopyDetail, count( gastroscopyDetailDetail LIKE '%"b":1%' OR NULL ) + count( gastroscopyDetailDetail LIKE '%"g":1%' OR NULL ) provinceEarlyCancer FROM ( SELECT p.id patientId, rel3.area provinceName, rel1.step gastroscopyStep, CASE rel1.is_inspect WHEN 1 THEN '是' WHEN 0 THEN '否' ELSE '' END isGastroscopy, rel1.inspect_way gastroscopyWay, CASE rel1.capsule WHEN 1 THEN '异常' WHEN 2 THEN '未见异常' ELSE '' END gastroscopyResult, rel1.precise_way gastroscopyDetailWay, CASE WHEN rel1.diagnose_result = 1 THEN '异常' WHEN rel1.diagnose_result = 2 THEN '未见明显异常' ELSE '' END gastroscopyDetailResult, rel1.diagnose_info gastroscopyDetailDetail, rel1.STATUS gastroscopyAuditStatus FROM tb_patient p RIGHT JOIN ( SELECT patient_id, step, inspect_way, precise_way, capsule, diagnose_result, diagnose_info, is_inspect, `status` FROM tb_gastro_inspect WHERE is_inspect = 1 AND `status` NOT IN ( 0, 5 ) ) rel1 ON rel1.patient_id = p.id INNER JOIN ( SELECT `name`, number FROM tb_hosp ) rel2 ON rel2.number = p.hosp_number LEFT JOIN ( SELECT area, `name` FROM tmp_tb_hosp_area ) rel3 ON rel3.`name` = rel2.`name` ) rel GROUP BY provinceName ) ) rel GROUP BY provinceName -- 页面血检地区统计（整理过程） SELECT 省份, 医联体编号, 医联体名称, sum( 计划血清检测总人次 + 实际血清检测总人次 ) 血清检测总人次, sum( 计划血清检测总人次 ) 计划血清检测总人次, sum( 实际血清检测总人次 ) 实际血清检测总人次, sum( HP抗体（IGG）检测阳性总人次 ) HP抗体（IGG）检测阳性总人次, sum( 早癌患者数 ) 早癌患者数 FROM ( ( SELECT 省份, 医联体编号, 医联体名称, count( * ) 计划血清检测总人次, count( NULL ) 实际血清检测总人次, count( NULL ) HP抗体（IGG）检测阳性总人次, count( NULL ) 早癌患者数 FROM ( SELECT p.id 患者id, rel3.area 省份, rel2.number 医联体编号, rel2.`name` 医联体名称, rel1.step 计划随访调查进度, CASE rel1.continue_flup WHEN 1 THEN '继续随访' WHEN 0 THEN '停止随访' ELSE '' END 随访状态, rel1.content_type 计划随访内容, rel1.`status` 审核状态 FROM tb_patient p RIGHT JOIN ( SELECT patient_id, step, continue_flup, content_type, `status` FROM tb_flup_plan WHERE continue_flup = 1 AND content_type LIKE '%b%' ) rel1 ON rel1.patient_id = p.id inner JOIN ( SELECT `name`, number FROM tb_hosp ) rel2 ON rel2.number = p.hosp_number LEFT JOIN ( SELECT area, `name` FROM tmp_tb_hosp_area ) rel3 ON rel3.`name` = rel2.`name` ) rel GROUP BY 医联体名称 ) UNION ( SELECT 省份, 医联体编号, 医联体名称, count( NULL ) 计划血清检测总人次, count( * ) 实际血清检测总人次, count( HP抗体（IGG）检测 = '阳性' OR NULL ) HP抗体（IGG）检测阳性总人次, count( NULL ) 早癌患者数 FROM ( SELECT p.id 患者id, rel3.area 省份, rel2.number 医联体编号, rel2.`name` 医联体名称, rel1.step 实验室检查调查进度, rel1.g17 'G-17', rel1.pg1 'PG-Ⅰ', rel1.pg2 'PG-Ⅱ', CASE rel1.igg WHEN 1 THEN '阳性' WHEN 0 THEN '阴性' ELSE '' END HP抗体（IGG）检测, rel1.`status` 审核状态 FROM tb_patient AS p RIGHT JOIN ( SELECT patient_id, step, g17, pg1, pg2, igg, `status` FROM tb_lab_inspect ) rel1 ON rel1.patient_id = p.id inner JOIN ( SELECT `name`, number FROM tb_hosp ) rel2 ON rel2.number = p.hosp_number LEFT JOIN ( SELECT area, `name` FROM tmp_tb_hosp_area ) rel3 ON rel3.`name` = rel2.`name` WHERE rel1.`status` NOT IN ( 0, 5 ) ) rel GROUP BY 医联体名称 ) UNION ( SELECT provinceName 省份, hospNumber 医联体编号, hospName 医联体名称, count( NULL ) 计划血清检测总人次, count( NULL ) 实际血清检测总人次, count( NULL ) HP抗体（IGG）检测阳性总人次, count( gastroDetailInfo LIKE '%"b":1%' OR NULL ) + count( gastroDetailInfo LIKE '%"g":1%' OR NULL ) 早癌患者数 FROM ( SELECT p.id patientId, rel3.area provinceName, rel2.number hospNumber, rel2.`name` hospName, rel1.step gastroStep, rel1.is_inspect isGastro, rel1.inspect_way gastroWay, rel1.capsule gastroResult, rel1.precise_way gastroDetailWay, rel1.diagnose_result gastroDetailResult, rel1.diagnose_info gastroDetailInfo, rel1.`status` auditStatus FROM tb_patient p RIGHT JOIN ( SELECT patient_id, step, is_inspect, inspect_way, capsule, precise_way, diagnose_result, diagnose_info, `status` FROM tb_gastro_inspect WHERE is_inspect = 1 AND `status` NOT IN ( 0, 5 ) AND diagnose_result = 1 ) rel1 ON rel1.patient_id = p.id inner JOIN ( SELECT `name`, number FROM tb_hosp ) rel2 ON rel2.number = p.hosp_number LEFT JOIN ( SELECT area, `name` FROM tmp_tb_hosp_area ) rel3 ON rel3.`name` = rel2.`name` ) rel GROUP BY 医联体名称 ) ) rel GROUP BY 医联体名称 -- 页面血检地区统计（结果） SELECT 省份, sum( 计划血清检测总人次 + 实际血清检测总人次 ) 血清检测总人次, sum( 计划血清检测总人次 ) 计划血清检测总人次, sum( 实际血清检测总人次 ) 实际血清检测总人次, sum( HP抗体（IGG）检测阳性总人次 ) HP抗体（IGG）检测阳性总人次, sum( 早癌患者数 ) 早癌患者数 FROM ( ( SELECT 省份, count( * ) 计划血清检测总人次, count( NULL ) 实际血清检测总人次, count( NULL ) HP抗体（IGG）检测阳性总人次, count( NULL ) 早癌患者数 FROM ( SELECT p.id 患者id, rel3.area 省份, rel1.step 计划随访调查进度, CASE rel1.continue_flup WHEN 1 THEN '继续随访' WHEN 0 THEN '停止随访' ELSE '' END 随访状态, rel1.content_type 计划随访内容, rel1.`status` 审核状态 FROM tb_patient p RIGHT JOIN ( SELECT patient_id, step, continue_flup, content_type, `status` FROM tb_flup_plan WHERE continue_flup = 1 AND content_type LIKE '%b%' ) rel1 ON rel1.patient_id = p.id inner JOIN ( SELECT `name`, number FROM tb_hosp ) rel2 ON rel2.number = p.hosp_number LEFT JOIN ( SELECT area, `name` FROM tmp_tb_hosp_area ) rel3 ON rel3.`name` = rel2.`name` ) rel GROUP BY 省份 ) UNION ( SELECT 省份, count( NULL ) 计划血清检测总人次, count( * ) 实际血清检测总人次, count( HP抗体（IGG）检测 = '阳性' OR NULL ) HP抗体（IGG）检测阳性总人次, count( NULL ) 早癌患者数 FROM ( SELECT p.id 患者id, rel3.area 省份, rel1.step 实验室检查调查进度, rel1.g17 'G-17', rel1.pg1 'PG-Ⅰ', rel1.pg2 'PG-Ⅱ', CASE rel1.igg WHEN 1 THEN '阳性' WHEN 0 THEN '阴性' ELSE '' END HP抗体（IGG）检测, rel1.`status` 审核状态 FROM tb_patient AS p RIGHT JOIN ( SELECT patient_id, step, g17, pg1, pg2, igg, `status` FROM tb_lab_inspect ) rel1 ON rel1.patient_id = p.id inner JOIN ( SELECT `name`, number FROM tb_hosp ) rel2 ON rel2.number = p.hosp_number LEFT JOIN ( SELECT area, `name` FROM tmp_tb_hosp_area ) rel3 ON rel3.`name` = rel2.`name` WHERE rel1.`status` NOT IN ( 0, 5 ) ) rel GROUP BY 省份 ) UNION ( SELECT provinceName 省份, count( NULL ) 计划血清检测总人次, count( NULL ) 实际血清检测总人次, count( NULL ) HP抗体（IGG）检测阳性总人次, count( gastroDetailInfo LIKE '%"b":1%' OR NULL ) + count( gastroDetailInfo LIKE '%"g":1%' OR NULL ) 早癌患者数 FROM ( SELECT p.id patientId, rel3.area provinceName, rel2.number hospNumber, rel2.`name` hospName, rel1.step gastroStep, rel1.is_inspect isGastro, rel1.inspect_way gastroWay, rel1.capsule gastroResult, rel1.precise_way gastroDetailWay, rel1.diagnose_result gastroDetailResult, rel1.diagnose_info gastroDetailInfo, rel1.`status` auditStatus FROM tb_patient p RIGHT JOIN ( SELECT patient_id, step, is_inspect, inspect_way, capsule, precise_way, diagnose_result, diagnose_info, `status` FROM tb_gastro_inspect WHERE is_inspect = 1 AND `status` NOT IN ( 0, 5 ) AND diagnose_result = 1 ) rel1 ON rel1.patient_id = p.id inner JOIN ( SELECT `name`, number FROM tb_hosp ) rel2 ON rel2.number = p.hosp_number LEFT JOIN ( SELECT area, `name` FROM tmp_tb_hosp_area ) rel3 ON rel3.`name` = rel2.`name` ) rel GROUP BY 省份 ) ) rel GROUP BY 省份 SELECT 省份, sum( 计划血清检测总人次 + 实际血清检测总人次 ) 血清检测总人次, sum( 计划血清检测总人次 ) 计划血清检测总人次, sum( 实际血清检测总人次 ) 实际血清检测总人次, sum( HP抗体（IGG）检测阳性总人次 ) HP抗体（IGG）检测阳性总人次, sum( 早癌患者数 ) 早癌患者数 FROM ( ( SELECT 省份, count( * ) 计划血清检测总人次, count( NULL ) 实际血清检测总人次, count( NULL ) HP抗体（IGG）检测阳性总人次, count( NULL ) 早癌患者数 FROM ( SELECT p.id 患者id, rel3.area 省份, rel1.step 计划随访调查进度, CASE rel1.continue_flup WHEN 1 THEN '继续随访' WHEN 0 THEN '停止随访' ELSE '' END 随访状态, rel1.content_type 计划随访内容, rel1.`status` 审核状态 FROM tb_patient p RIGHT JOIN ( SELECT patient_id, step, continue_flup, content_type, `status` FROM tb_flup_plan WHERE continue_flup = 1 AND content_type LIKE '%b%' ) rel1 ON rel1.patient_id = p.id inner JOIN ( SELECT `name`, number FROM tb_hosp ) rel2 ON rel2.number = p.hosp_number LEFT JOIN ( SELECT area, `name` FROM tmp_tb_hosp_area ) rel3 ON rel3.`name` = rel2.`name` ) rel GROUP BY 省份 ) UNION ( SELECT 省份, count( NULL ) 计划血清检测总人次, count( * ) 实际血清检测总人次, count( HP抗体（IGG）检测 = '阳性' OR NULL ) HP抗体（IGG）检测阳性总人次, count( NULL ) 早癌患者数 FROM ( SELECT p.id 患者id, rel3.area 省份, rel1.step 实验室检查调查进度, rel1.g17 'G-17', rel1.pg1 'PG-Ⅰ', rel1.pg2 'PG-Ⅱ', CASE rel1.igg WHEN 1 THEN '阳性' WHEN 0 THEN '阴性' ELSE '' END HP抗体（IGG）检测, rel1.`status` 审核状态 FROM tb_patient AS p RIGHT JOIN ( SELECT patient_id, step, g17, pg1, pg2, igg, `status` FROM tb_lab_inspect ) rel1 ON rel1.patient_id = p.id inner JOIN ( SELECT `name`, number FROM tb_hosp ) rel2 ON rel2.number = p.hosp_number LEFT JOIN ( SELECT area, `name` FROM tmp_tb_hosp_area ) rel3 ON rel3.`name` = rel2.`name` WHERE rel1.`status` NOT IN ( 0, 5 ) ) rel GROUP BY 省份 ) UNION ( SELECT provinceName 省份, count( NULL ) 计划血清检测总人次, count( NULL ) 实际血清检测总人次, count( NULL ) HP抗体（IGG）检测阳性总人次, count( gastroDetailInfo LIKE '%"b":1%' OR NULL ) + count( gastroDetailInfo LIKE '%"g":1%' OR NULL ) 早癌患者数 FROM ( SELECT p.id patientId, rel3.area provinceName, rel2.number hospNumber, rel2.`name` hospName, rel1.step gastroStep, rel1.is_inspect isGastro, rel1.inspect_way gastroWay, rel1.capsule gastroResult, rel1.precise_way gastroDetailWay, rel1.diagnose_result gastroDetailResult, rel1.diagnose_info gastroDetailInfo, rel1.`status` auditStatus FROM tb_patient p RIGHT JOIN ( SELECT patient_id, step, is_inspect, inspect_way, capsule, precise_way, diagnose_result, diagnose_info, `status` FROM tb_gastro_inspect WHERE is_inspect = 1 AND `status` NOT IN ( 0, 5 ) AND diagnose_result = 1 ) rel1 ON rel1.patient_id = p.id inner JOIN ( SELECT `name`, number FROM tb_hosp ) rel2 ON rel2.number = p.hosp_number LEFT JOIN ( SELECT area, `name` FROM tmp_tb_hosp_area ) rel3 ON rel3.`name` = rel2.`name` ) rel GROUP BY 省份 ) ) rel GROUP BY 省份 SELECT provinceName, sum( provinceSerumTestPlan + provinceSerumTestActual ) provinceSerumTestTotal, sum( provinceSerumTestPlan ) provinceSerumTestPlan, sum( provinceSerumTestActual ) provinceSerumTestActual, sum( provinceIggPositive ) provinceIggPositive, sum( provinceEarlyCancer ) provinceEarlyCancer FROM ( ( SELECT provinceName, count( * ) provinceSerumTestPlan, count( NULL ) provinceSerumTestActual, count( NULL ) provinceIggPositive, count( NULL ) provinceEarlyCancer FROM ( SELECT p.id patientId, rel3.area provinceName, rel1.step planFlupStep, CASE rel1.continue_flup WHEN 1 THEN '继续随访' WHEN 0 THEN '停止随访' ELSE '' END isContinueFlup, rel1.content_type planFlupContent, rel1.`status` planFlupAuditStatus FROM tb_patient p RIGHT JOIN ( SELECT patient_id, step, continue_flup, content_type, `status` FROM tb_flup_plan WHERE continue_flup = 1 AND content_type LIKE '%b%' ) rel1 ON rel1.patient_id = p.id INNER JOIN ( SELECT `name`, number FROM tb_hosp ) rel2 ON rel2.number = p.hosp_number LEFT JOIN ( SELECT area, `name` FROM tmp_tb_hosp_area ) rel3 ON rel3.`name` = rel2.`name` ) rel GROUP BY provinceName ) UNION ( SELECT provinceName, count( NULL ) provinceSerumTestPlan, count( * ) provinceSerumTestActual, count( iggDetection = '阳性' OR NULL ) provinceIggPositive, count( NULL ) provinceEarlyCancer FROM ( SELECT p.id patientId, rel3.area provinceName, rel1.step labInspectStep, rel1.g17 G17, rel1.pg1 PG1, rel1.pg2 PG2, CASE rel1.igg WHEN 1 THEN '阳性' WHEN 0 THEN '阴性' ELSE '' END iggDetection, rel1.`status` labInspectStatus FROM tb_patient AS p RIGHT JOIN ( SELECT patient_id, step, g17, pg1, pg2, igg, `status` FROM tb_lab_inspect ) rel1 ON rel1.patient_id = p.id INNER JOIN ( SELECT `name`, number FROM tb_hosp ) rel2 ON rel2.number = p.hosp_number LEFT JOIN ( SELECT area, `name` FROM tmp_tb_hosp_area ) rel3 ON rel3.`name` = rel2.`name` WHERE rel1.`status` NOT IN ( 0, 5 ) ) rel GROUP BY provinceName ) UNION ( SELECT provinceName, count( NULL ) provinceSerumTestPlan, count( NULL ) provinceSerumTestActual, count( NULL ) provinceIggPositive, count( gastroscopyDetailInfo LIKE '%"b":1%' OR NULL ) + count( gastroscopyDetailInfo LIKE '%"g":1%' OR NULL ) provinceEarlyCancer FROM ( SELECT p.id patientId, rel3.area provinceName, rel1.step gastroscopyStep, rel1.is_inspect isGastroscopy, rel1.inspect_way gastroscopyWay, rel1.capsule gastroscopyResult, rel1.precise_way gastroscopyDetailWay, rel1.diagnose_result gastroscopyDetailResult, rel1.diagnose_info gastroscopyDetailInfo, rel1.`status` gastroscopyAuditStatus FROM tb_patient p RIGHT JOIN ( SELECT patient_id, step, is_inspect, inspect_way, capsule, precise_way, diagnose_result, diagnose_info, `status` FROM tb_gastro_inspect WHERE is_inspect = 1 AND `status` NOT IN ( 0, 5 ) AND diagnose_result = 1 ) rel1 ON rel1.patient_id = p.id INNER JOIN ( SELECT `name`, number FROM tb_hosp ) rel2 ON rel2.number = p.hosp_number LEFT JOIN ( SELECT area, `name` FROM tmp_tb_hosp_area ) rel3 ON rel3.`name` = rel2.`name` ) rel GROUP BY provinceName ) ) rel GROUP BY provinceName -- 页面筛查数据统计结果（省份） SELECT 省份, 医联体编号, 医联体名称, count( 筛查状态 = 1 OR NULL ) 筛查申请数, count( * ) 筛查总数 FROM ( SELECT p.id 患者id, p.`name` 患者姓名, rel6.area 省份, rel5.number 医联体编号, rel5.`name` 医联体名称, CASE rel1.igg WHEN 1 THEN '阳性' WHEN 0 THEN '阴性' ELSE '' END HP抗体（IGG）检测, CASE rel2.grade WHEN 1 THEN '低风险' WHEN 2 THEN '中风险' WHEN 3 THEN '高风险' ELSE '' END 胃癌患病风险等级, CASE rel3.is_inspect WHEN 1 THEN '是' WHEN 0 THEN '否' ELSE '否' END 是否胃镜检查, rel3.inspect_way 胃镜检查方式, CASE rel3.capsule WHEN 1 THEN '异常' WHEN 2 THEN '未见异常' ELSE '' END 胃镜检查结果, rel3.precise_way 胃镜精查方式, CASE rel3.diagnose_result WHEN 1 THEN '异常' WHEN 2 THEN '未见明显异常' ELSE '' END 最终诊断结果, rel3.diagnose_info 最终诊断详情, CASE rel4.continue_flup WHEN 1 THEN '继续随访' WHEN 0 THEN '停止随访' ELSE '' END 是否继续随访, rel4.content_type 继续随访内容, rel1.`status` 筛查状态 FROM tb_patient p RIGHT JOIN ( SELECT patient_id, igg, step, `status` FROM tb_lab_inspect WHERE `status` NOT IN ( 0, 5 ) ) rel1 ON rel1.patient_id = p.id LEFT JOIN ( SELECT patient_id, step, score, grade, `status` FROM tb_risk_assess WHERE `status` NOT IN ( 0, 5 ) ) rel2 ON rel2.patient_id = p.id AND rel2.step = rel1.step AND rel2.`status` = rel1.`status` LEFT JOIN ( SELECT patient_id, step, is_inspect, inspect_way, capsule, precise_way, diagnose_result, diagnose_info, `status` FROM tb_gastro_inspect WHERE `status` NOT IN ( 0, 5 ) ) rel3 ON rel3.patient_id = p.id AND rel3.step = rel2.step AND rel3.`status` = rel2.`status` LEFT JOIN ( SELECT patient_id, step, continue_flup, content_type, `status` FROM tb_flup_plan WHERE `status` NOT IN ( 0, 5 ) ) rel4 ON rel4.patient_id = p.id AND rel4.step = rel3.step AND rel4.`status` = rel3.`status` inner JOIN ( SELECT number, `name` FROM tb_hosp ) rel5 ON rel5.number = p.hosp_number LEFT JOIN ( SELECT area, `name` FROM tmp_tb_hosp_area ) rel6 ON rel6.`name` = rel5.`name` ) rel GROUP BY 医联体名称 SELECT 省份, count( 筛查状态 = 1 OR NULL ) 筛查申请数, count( * ) 筛查总数 FROM ( SELECT p.id 患者id, p.`name` 患者姓名, rel6.area 省份, CASE rel1.igg WHEN 1 THEN '阳性' WHEN 0 THEN '阴性' ELSE '' END HP抗体（IGG）检测, CASE rel2.grade WHEN 1 THEN '低风险' WHEN 2 THEN '中风险' WHEN 3 THEN '高风险' ELSE '' END 胃癌患病风险等级, CASE rel3.is_inspect WHEN 1 THEN '是' WHEN 0 THEN '否' ELSE '否' END 是否胃镜检查, rel3.inspect_way 胃镜检查方式, CASE rel3.capsule WHEN 1 THEN '异常' WHEN 2 THEN '未见异常' ELSE '' END 胃镜检查结果, rel3.precise_way 胃镜精查方式, CASE rel3.diagnose_result WHEN 1 THEN '异常' WHEN 2 THEN '未见明显异常' ELSE '' END 最终诊断结果, rel3.diagnose_info 最终诊断详情, CASE rel4.continue_flup WHEN 1 THEN '继续随访' WHEN 0 THEN '停止随访' ELSE '' END 是否继续随访, rel4.content_type 继续随访内容, rel1.`status` 筛查状态 FROM tb_patient p RIGHT JOIN ( SELECT patient_id, igg, step, `status` FROM tb_lab_inspect WHERE `status` NOT IN ( 0, 5 ) ) rel1 ON rel1.patient_id = p.id LEFT JOIN ( SELECT patient_id, step, score, grade, `status` FROM tb_risk_assess WHERE `status` NOT IN ( 0, 5 ) ) rel2 ON rel2.patient_id = p.id AND rel2.step = rel1.step AND rel2.`status` = rel1.`status` LEFT JOIN ( SELECT patient_id, step, is_inspect, inspect_way, capsule, precise_way, diagnose_result, diagnose_info, `status` FROM tb_gastro_inspect WHERE `status` NOT IN ( 0, 5 ) ) rel3 ON rel3.patient_id = p.id AND rel3.step = rel2.step AND rel3.`status` = rel2.`status` LEFT JOIN ( SELECT patient_id, step, continue_flup, content_type, `status` FROM tb_flup_plan WHERE `status` NOT IN ( 0, 5 ) ) rel4 ON rel4.patient_id = p.id AND rel4.step = rel3.step AND rel4.`status` = rel3.`status` inner JOIN ( SELECT number, `name` FROM tb_hosp ) rel5 ON rel5.number = p.hosp_number LEFT JOIN ( SELECT area, `name` FROM tmp_tb_hosp_area ) rel6 ON rel6.`name` = rel5.`name` ) rel GROUP BY 省份 SELECT provinceName, count( * ) provinceScreeningTotal, count( screeningStatus = 1 OR NULL ) provinceApplyScreeningTotal FROM ( SELECT p.id patientId, p.`name` patientName, rel6.area provinceName, CASE rel1.igg WHEN 1 THEN '阳性' WHEN 0 THEN '阴性' ELSE '' END iggDetection, CASE rel2.grade WHEN 1 THEN '低风险' WHEN 2 THEN '中风险' WHEN 3 THEN '高风险' ELSE '' END riskLevel, CASE rel3.is_inspect WHEN 1 THEN '是' WHEN 0 THEN '否' ELSE '否' END isGastroscopy, rel3.inspect_way gastroscopyWay, CASE rel3.capsule WHEN 1 THEN '异常' WHEN 2 THEN '未见异常' ELSE '' END gastroscopyResult, rel3.precise_way gastroscopyDetailWay, CASE rel3.diagnose_result WHEN 1 THEN '异常' WHEN 2 THEN '未见明显异常' ELSE '' END gastroscopyDetailResult, rel3.diagnose_info gastroscopyDetailInfo, CASE rel4.continue_flup WHEN 1 THEN '继续随访' WHEN 0 THEN '停止随访' ELSE '' END isContinueFlup, rel4.content_type planFlupContent, rel1.`status` screeningStatus FROM tb_patient p RIGHT JOIN ( SELECT patient_id, igg, step, `status` FROM tb_lab_inspect WHERE `status` NOT IN ( 0, 5 ) ) rel1 ON rel1.patient_id = p.id LEFT JOIN ( SELECT patient_id, step, score, grade, `status` FROM tb_risk_assess WHERE `status` NOT IN ( 0, 5 ) ) rel2 ON rel2.patient_id = p.id AND rel2.step = rel1.step AND rel2.`status` = rel1.`status` LEFT JOIN ( SELECT patient_id, step, is_inspect, inspect_way, capsule, precise_way, diagnose_result, diagnose_info, `status` FROM tb_gastro_inspect WHERE `status` NOT IN ( 0, 5 ) ) rel3 ON rel3.patient_id = p.id AND rel3.step = rel2.step AND rel3.`status` = rel2.`status` LEFT JOIN ( SELECT patient_id, step, continue_flup, content_type, `status` FROM tb_flup_plan WHERE `status` NOT IN ( 0, 5 ) ) rel4 ON rel4.patient_id = p.id AND rel4.step = rel3.step AND rel4.`status` = rel3.`status` INNER JOIN ( SELECT number, `name` FROM tb_hosp ) rel5 ON rel5.number = p.hosp_number LEFT JOIN ( SELECT area, `name` FROM tmp_tb_hosp_area ) rel6 ON rel6.`name` = rel5.`name` ) rel GROUP BY provinceName -- 各省份胃镜检查诊断结果统计 SELECT provinceName 省份, ( count( gastroDetailInfo LIKE '%"a":1%' OR NULL ) + count( gastroDetailInfo LIKE '%"b":1%' OR NULL ) + count( gastroDetailInfo LIKE '%"c":1%' OR NULL ) + count( gastroDetailInfo LIKE '%"d":1%' OR NULL ) + count( gastroDetailInfo LIKE '%"e":1%' OR NULL ) + count( gastroDetailInfo LIKE '%"f":1%' OR NULL ) + count( gastroDetailInfo LIKE '%"g":1%' OR NULL ) + count( gastroDetailInfo LIKE '%"h":1%' OR NULL ) + count( gastroDetailInfo LIKE '%"i":1%' OR NULL ) + count( gastroDetailInfo LIKE '%"j":1%' OR NULL ) + count( gastroDetailInfo LIKE '%"k":1%' OR NULL ) + count( gastroDetailInfo LIKE '%"l":1%' OR NULL ) + count( gastroDetailInfo LIKE '%"m":1%' OR NULL ) + count( gastroDetailInfo LIKE '%"n":%' OR NULL ) ) 合计, count( gastroDetailInfo LIKE '%"a":1%' OR NULL ) AS 食管粘膜上皮内瘤变, count( gastroDetailInfo LIKE '%"b":1%' OR NULL ) AS 早期食管癌, count( gastroDetailInfo LIKE '%"c":1%' OR NULL ) AS 进展期食管癌, count( gastroDetailInfo LIKE '%"d":1%' OR NULL ) AS Barret食管, count( gastroDetailInfo LIKE '%"e":1%' OR NULL ) AS 反流性食管炎, count( gastroDetailInfo LIKE '%"f":1%' OR NULL ) AS 胃粘膜上皮内瘤变, count( gastroDetailInfo LIKE '%"g":1%' OR NULL ) AS 早期胃癌, count( gastroDetailInfo LIKE '%"h":1%' OR NULL ) AS 进展期胃癌, count( gastroDetailInfo LIKE '%"i":1%' OR NULL ) AS 萎缩性胃炎, count( gastroDetailInfo LIKE '%"j":1%' OR NULL ) AS 胃溃疡, count( gastroDetailInfo LIKE '%"k":1%' OR NULL ) AS 胃息肉, count( gastroDetailInfo LIKE '%"l":1%' OR NULL ) AS 非萎缩性胃炎, count( gastroDetailInfo LIKE '%"m":1%' OR NULL ) AS 十二指肠球部溃疡, count( gastroDetailInfo LIKE '%"n":%' OR NULL ) AS 其它病变 FROM ( SELECT p.id patientId, rel3.area provinceName, rel1.step gastroStep, rel1.is_inspect isGastro, rel1.inspect_way gastroWay, rel1.capsule gastroResult, rel1.precise_way gastroDetailWay, rel1.diagnose_result gastroDetailResult, rel1.diagnose_info gastroDetailInfo, rel1.`status` auditStatus FROM tb_patient p RIGHT JOIN ( SELECT patient_id, step, is_inspect, inspect_way, capsule, precise_way, diagnose_result, diagnose_info, `status` FROM tb_gastro_inspect WHERE is_inspect = 1 AND `status` NOT IN ( 0, 5 ) AND diagnose_result = 1 ) rel1 ON rel1.patient_id = p.id INNER JOIN ( SELECT `name`, number FROM tb_hosp ) rel2 ON rel2.number = p.hosp_number LEFT JOIN ( SELECT area, `name` FROM tmp_tb_hosp_area ) rel3 ON rel3.`name` = rel2.`name` ) rel GROUP BY provinceName -- 页面胃癌患病风险等级评估统计 SELECT 省份, (count( 胃癌患病风险等级 = '低风险' OR NULL ) + count( 胃癌患病风险等级 = '中风险' OR NULL ) + count( 胃癌患病风险等级 = '高风险' OR NULL )) 合计, count( 胃癌患病风险等级 = '低风险' OR NULL ) AS 低风险, count( 胃癌患病风险等级 = '中风险' OR NULL ) AS 中风险, count( 胃癌患病风险等级 = '高风险' OR NULL ) AS 高风险 FROM ( SELECT p.id 患者id, rel3.area 省份, rel1.score 胃癌患病风险评分, CASE rel1.grade WHEN 1 THEN '低风险' WHEN 2 THEN '中风险' WHEN 3 THEN '高风险' ELSE '' END 胃癌患病风险等级 FROM tb_patient p RIGHT JOIN ( SELECT patient_id, score, grade FROM tb_risk_assess ) rel1 ON rel1.patient_id = p.id INNER JOIN ( SELECT `name`, number FROM tb_hosp ) rel2 ON rel2.number = p.hosp_number LEFT JOIN ( SELECT area, `name` FROM tmp_tb_hosp_area ) rel3 ON rel3.`name` = rel2.`name` ) rel GROUP BY 省份 SELECT count( riskLevel = '高风险' OR NULL ) highRisk, count( riskLevel = '中风险' OR NULL ) mediumRisk, count( riskLevel = '低风险' OR NULL ) lowRisk FROM ( SELECT p.id patientId, rel1.score riskScore, CASE rel1.grade WHEN 1 THEN '低风险' WHEN 2 THEN '中风险' WHEN 3 THEN '高风险' ELSE '' END riskLevel FROM tb_patient p RIGHT JOIN ( SELECT patient_id, igg, step, `status` FROM tb_lab_inspect WHERE `status` NOT IN ( 0, 5 ) ) rel4 ON rel4.patient_id = p.id LEFT JOIN ( SELECT patient_id, score, grade, step, `status` FROM tb_risk_assess WHERE `status` NOT IN ( 0, 5 ) ) rel1 ON rel1.patient_id = p.id AND rel1.step = rel4.step AND rel1.`status` = rel4.`status` INNER JOIN ( SELECT `name`, number FROM tb_hosp ) rel2 ON rel2.number = p.hosp_number LEFT JOIN ( SELECT area, `name` FROM tmp_tb_hosp_area ) rel3 ON rel3.`name` = rel2.`name` ) rel SELECT 省份, count( 筛查状态 = 1 OR NULL ) 筛查申请数, count( * ) 筛查总数 FROM ( SELECT p.id 患者id, p.`name` 患者姓名, rel6.area 省份, CASE rel1.igg WHEN 1 THEN '阳性' WHEN 0 THEN '阴性' ELSE '' END HP抗体（IGG）检测, CASE rel2.grade WHEN 1 THEN '低风险' WHEN 2 THEN '中风险' WHEN 3 THEN '高风险' ELSE '' END 胃癌患病风险等级, rel2.score 胃癌患病评分, CASE rel3.is_inspect WHEN 1 THEN '是' WHEN 0 THEN '否' ELSE '否' END 是否胃镜检查, rel3.inspect_way 胃镜检查方式, CASE rel3.capsule WHEN 1 THEN '异常' WHEN 2 THEN '未见异常' ELSE '' END 胃镜检查结果, rel3.precise_way 胃镜精查方式, CASE rel3.diagnose_result WHEN 1 THEN '异常' WHEN 2 THEN '未见明显异常' ELSE '' END 最终诊断结果, rel3.diagnose_info 最终诊断详情, CASE rel4.continue_flup WHEN 1 THEN '继续随访' WHEN 0 THEN '停止随访' ELSE '' END 是否继续随访, rel4.content_type 继续随访内容, rel1.`status` 筛查状态 FROM tb_patient p RIGHT JOIN ( SELECT patient_id, igg, step, `status` FROM tb_lab_inspect WHERE `status` NOT IN ( 0, 5 ) ) rel1 ON rel1.patient_id = p.id LEFT JOIN ( SELECT patient_id, step, score, grade, `status` FROM tb_risk_assess WHERE `status` NOT IN ( 0, 5 ) ) rel2 ON rel2.patient_id = p.id AND rel2.step = rel1.step AND rel2.`status` = rel1.`status` LEFT JOIN ( SELECT patient_id, step, is_inspect, inspect_way, capsule, precise_way, diagnose_result, diagnose_info, `status` FROM tb_gastro_inspect WHERE `status` NOT IN ( 0, 5 ) ) rel3 ON rel3.patient_id = p.id AND rel3.step = rel2.step AND rel3.`status` = rel2.`status` LEFT JOIN ( SELECT patient_id, step, continue_flup, content_type, `status` FROM tb_flup_plan WHERE `status` NOT IN ( 0, 5 ) ) rel4 ON rel4.patient_id = p.id AND rel4.step = rel3.step AND rel4.`status` = rel3.`status` inner JOIN ( SELECT number, `name` FROM tb_hosp ) rel5 ON rel5.number = p.hosp_number LEFT JOIN ( SELECT area, `name` FROM tmp_tb_hosp_area ) rel6 ON rel6.`name` = rel5.`name` ) rel GROUP BY 省份 select * from (SELECT p.id patientId, p.`name` 患者姓名, rel6.area 省份, CASE rel1.igg WHEN 1 THEN '阳性' WHEN 0 THEN '阴性' ELSE '' END HP抗体（IGG）检测, CASE rel2.grade WHEN 1 THEN '低风险' WHEN 2 THEN '中风险' WHEN 3 THEN '高风险' ELSE '' END 胃癌患病风险等级, CASE rel3.is_inspect WHEN 1 THEN '是' WHEN 0 THEN '否' ELSE '否' END 是否胃镜检查, rel3.inspect_way 胃镜检查方式, CASE rel3.capsule WHEN 1 THEN '异常' WHEN 2 THEN '未见异常' ELSE '' END 胃镜检查结果, rel3.precise_way 胃镜精查方式, CASE rel3.diagnose_result WHEN 1 THEN '异常' WHEN 2 THEN '未见明显异常' ELSE '' END 最终诊断结果, rel3.diagnose_info 最终诊断详情, CASE rel4.continue_flup WHEN 1 THEN '继续随访' WHEN 0 THEN '停止随访' ELSE '' END 是否继续随访, rel4.content_type 继续随访内容, rel1.`status` 筛查状态 FROM tb_patient p RIGHT JOIN ( SELECT patient_id, igg, step, `status` FROM tb_lab_inspect WHERE `status` NOT IN ( 0, 5 ) ) rel1 ON rel1.patient_id = p.id LEFT JOIN ( SELECT patient_id, step, score, grade, `status` FROM tb_risk_assess WHERE `status` NOT IN ( 0, 5 ) ) rel2 ON rel2.patient_id = p.id AND rel2.step = rel1.step AND rel2.`status` = rel1.`status` LEFT JOIN ( SELECT patient_id, step, is_inspect, inspect_way, capsule, precise_way, diagnose_result, diagnose_info, `status` FROM tb_gastro_inspect WHERE `status` NOT IN ( 0, 5 ) ) rel3 ON rel3.patient_id = p.id AND rel3.step = rel2.step AND rel3.`status` = rel2.`status` LEFT JOIN ( SELECT patient_id, step, continue_flup, content_type, `status` FROM tb_flup_plan WHERE `status` NOT IN ( 0, 5 ) ) rel4 ON rel4.patient_id = p.id AND rel4.step = rel3.step AND rel4.`status` = rel3.`status` inner JOIN ( SELECT number, `name` FROM tb_hosp ) rel5 ON rel5.number = p.hosp_number LEFT JOIN ( SELECT area, `name` FROM tmp_tb_hosp_area ) rel6 ON rel6.`name` = rel5.`name`) rel where rel.胃癌患病风险等级 <> '' SELECT provinceName, sum( serumTestPlan + serumTestActual ) serumTestTotal, sum( serumTestPlan ) serumTestPlan, sum( serumTestActual ) serumTestActual, sum( iggPositive ) iggPositive, sum( earlyCancer ) earlyCancer FROM ( ( SELECT provinceName, count( * ) serumTestPlan, count( NULL ) serumTestActual, count( NULL ) iggPositive, count( NULL ) earlyCancer FROM ( SELECT p.id patientId, rel3.area provinceName, rel1.step planFlupStep, CASE rel1.continue_flup WHEN 1 THEN '继续随访' WHEN 0 THEN '停止随访' ELSE '' END isContinueFlup, rel1.content_type planFlupContent, rel1.`status` planFlupAuditStatus FROM tb_patient p RIGHT JOIN ( SELECT patient_id, step, continue_flup, content_type, `status` FROM tb_flup_plan WHERE continue_flup = 1 AND content_type LIKE '%b%' ) rel1 ON rel1.patient_id = p.id INNER JOIN ( SELECT `name`, number FROM tb_hosp ) rel2 ON rel2.number = p.hosp_number LEFT JOIN ( SELECT area, `name` FROM tmp_tb_hosp_area ) rel3 ON rel3.`name` = rel2.`name` ) rel GROUP BY provinceName ) UNION ( SELECT provinceName, count( NULL ) serumTestPlan, count( * ) serumTestActual, count( iggDetection = '阳性' OR NULL ) iggPositive, count( NULL ) earlyCancer FROM ( SELECT p.id patientId, rel3.area provinceName, rel1.step labInspectStep, rel1.g17 G17, rel1.pg1 PG1, rel1.pg2 PG2, CASE rel1.igg WHEN 1 THEN '阳性' WHEN 0 THEN '阴性' ELSE '' END iggDetection, rel1.`status` labInspectStatus FROM tb_patient AS p RIGHT JOIN ( SELECT patient_id, step, g17, pg1, pg2, igg, `status` FROM tb_lab_inspect ) rel1 ON rel1.patient_id = p.id INNER JOIN ( SELECT `name`, number FROM tb_hosp ) rel2 ON rel2.number = p.hosp_number LEFT JOIN ( SELECT area, `name` FROM tmp_tb_hosp_area ) rel3 ON rel3.`name` = rel2.`name` WHERE rel1.`status` NOT IN ( 0, 5 ) ) rel GROUP BY provinceName ) UNION ( SELECT provinceName, count( NULL ) serumTestPlan, count( NULL ) serumTestActual, count( NULL ) iggPositive, count( gastroscopyDetailInfo LIKE '%"b":1%' OR NULL ) + count( gastroscopyDetailInfo LIKE '%"g":1%' OR NULL ) earlyCancer FROM ( SELECT p.id patientId, rel3.area provinceName, rel1.step gastroscopyStep, rel1.is_inspect isGastroscopy, rel1.inspect_way gastroscopyWay, rel1.capsule gastroscopyResult, rel1.precise_way gastroscopyDetailWay, rel1.diagnose_result gastroscopyDetailResult, rel1.diagnose_info gastroscopyDetailInfo, rel1.`status` gastroscopyAuditStatus FROM tb_patient p RIGHT JOIN ( SELECT patient_id, step, is_inspect, inspect_way, capsule, precise_way, diagnose_result, diagnose_info, `status` FROM tb_gastro_inspect WHERE is_inspect = 1 AND `status` NOT IN ( 0, 5 ) AND diagnose_result = 1 ) rel1 ON rel1.patient_id = p.id INNER JOIN ( SELECT `name`, number FROM tb_hosp ) rel2 ON rel2.number = p.hosp_number LEFT JOIN ( SELECT area, `name` FROM tmp_tb_hosp_area ) rel3 ON rel3.`name` = rel2.`name` ) rel GROUP BY provinceName ) ) rel GROUP BY provinceName SELECT p.id patientId, p.`name` patientName, rel6.area provinceName, CASE rel1.igg WHEN 1 THEN '阳性' WHEN 0 THEN '阴性' ELSE '' END iggDetection, CASE rel2.grade WHEN 1 THEN '低风险' WHEN 2 THEN '中风险' WHEN 3 THEN '高风险' ELSE '' END riskLevel, CASE rel3.is_inspect WHEN 1 THEN '是' WHEN 0 THEN '否' ELSE '否' END isGastroscopy, rel3.inspect_way gastroscopyWay, CASE rel3.capsule WHEN 1 THEN '异常' WHEN 2 THEN '未见异常' ELSE '' END gastroscopyResult, rel3.precise_way gastroscopyDetailWay, CASE rel3.diagnose_result WHEN 1 THEN '异常' WHEN 2 THEN '未见明显异常' ELSE '' END gastroscopyDetailResult, rel3.diagnose_info gastroscopyDetailInfo, rel1.`status` screeningStatus FROM tb_patient p RIGHT JOIN ( SELECT patient_id, igg, step, `status` FROM tb_lab_inspect WHERE `status` NOT IN ( 0, 5 ) ) rel1 ON rel1.patient_id = p.id LEFT JOIN ( SELECT patient_id, step, score, grade, `status` FROM tb_risk_assess) rel2 ON rel2.patient_id = p.id AND rel2.step = rel1.step AND rel2.`status` = rel1.`status` LEFT JOIN ( SELECT patient_id, step, is_inspect, inspect_way, capsule, precise_way, diagnose_result, diagnose_info, `status` FROM tb_gastro_inspect ) rel3 ON rel3.patient_id = p.id AND rel3.step = rel2.step AND rel3.`status` = rel2.`status` INNER JOIN ( SELECT number, `name` FROM tb_hosp ) rel5 ON rel5.number = p.hosp_number LEFT JOIN ( SELECT area, `name` FROM tmp_tb_hosp_area ) rel6 ON rel6.`name` = rel5.`name` order by patientId desc where rel2.grade = 2 or rel2.grade = 3 SELECT count( * ) total, count( gastroscopyResult = '异常' OR NULL ) actual FROM ( SELECT p.id patientId, p.`name` patientName, rel6.area provinceName, CASE rel1.igg WHEN 1 THEN '阳性' WHEN 0 THEN '阴性' ELSE '' END iggDetection, CASE rel2.grade WHEN 1 THEN '低风险' WHEN 2 THEN '中风险' WHEN 3 THEN '高风险' ELSE '' END riskLevel, CASE rel3.is_inspect WHEN 1 THEN '是' WHEN 0 THEN '否' ELSE '否' END isGastroscopy, rel3.inspect_way gastroscopyWay, CASE rel3.capsule WHEN 1 THEN '异常' WHEN 2 THEN '未见异常' ELSE '' END gastroscopyResult, rel3.precise_way gastroscopyDetailWay, CASE rel3.diagnose_result WHEN 1 THEN '异常' WHEN 2 THEN '未见明显异常' ELSE '' END gastroscopyDetailResult, rel3.diagnose_info gastroscopyDetailInfo, rel1.`status` screeningStatus FROM tb_patient p RIGHT JOIN ( SELECT patient_id, igg, step, `status` FROM tb_lab_inspect WHERE `status` NOT IN ( 0, 5 ) ) rel1 ON rel1.patient_id = p.id LEFT JOIN ( SELECT patient_id, step, score, grade, `status` FROM tb_risk_assess ) rel2 ON rel2.patient_id = p.id AND rel2.step = rel1.step AND rel2.`status` = rel1.`status` LEFT JOIN ( SELECT patient_id, step, is_inspect, inspect_way, capsule, precise_way, diagnose_result, diagnose_info, `status` FROM tb_gastro_inspect ) rel3 ON rel3.patient_id = p.id AND rel3.step = rel2.step AND rel3.`status` = rel2.`status` INNER JOIN ( SELECT number, `name` FROM tb_hosp ) rel5 ON rel5.number = p.hosp_number LEFT JOIN ( SELECT area, `name` FROM tmp_tb_hosp_area ) rel6 ON rel6.`name` = rel5.`name` WHERE rel2.grade = 2 OR rel2.grade = 3 ) rel SELECT count( is_auddit = 1 OR NULL ) certifiedHosp, count( is_auddit = 0 OR NULL ) applyHosp FROM tmp_tb_hosp_apply SELECT provinceName, count( * ) provinceScreeningTotal, count( screeningStatus = 1 OR NULL ) provinceApplyScreeningTotal, count( hospaudditStatus = 0 or null ) hospApply FROM ( SELECT p.id patientId, p.`name` patientName, rel6.area provinceName, CASE rel1.igg WHEN 1 THEN '阳性' WHEN 0 THEN '阴性' ELSE '' END iggDetection, CASE rel2.grade WHEN 1 THEN '低风险' WHEN 2 THEN '中风险' WHEN 3 THEN '高风险' ELSE '' END riskLevel, CASE rel3.is_inspect WHEN 1 THEN '是' WHEN 0 THEN '否' ELSE '否' END isGastroscopy, rel3.inspect_way gastroscopyWay, CASE rel3.capsule WHEN 1 THEN '异常' WHEN 2 THEN '未见异常' ELSE '' END gastroscopyResult, rel3.precise_way gastroscopyDetailWay, CASE rel3.diagnose_result WHEN 1 THEN '异常' WHEN 2 THEN '未见明显异常' ELSE '' END gastroscopyDetailResult, rel3.diagnose_info gastroscopyDetailInfo, CASE rel4.continue_flup WHEN 1 THEN '继续随访' WHEN 0 THEN '停止随访' ELSE '' END isContinueFlup, rel4.content_type planFlupContent, rel1.`status` screeningStatus, rel7.is_auddit hospAudditStatus FROM tb_patient p RIGHT JOIN ( SELECT patient_id, igg, step, `status` FROM tb_lab_inspect WHERE `status` NOT IN ( 0, 5 ) ) rel1 ON rel1.patient_id = p.id LEFT JOIN ( SELECT patient_id, step, score, grade, `status` FROM tb_risk_assess WHERE `status` NOT IN ( 0, 5 ) ) rel2 ON rel2.patient_id = p.id AND rel2.step = rel1.step AND rel2.`status` = rel1.`status` LEFT JOIN ( SELECT patient_id, step, is_inspect, inspect_way, capsule, precise_way, diagnose_result, diagnose_info, `status` FROM tb_gastro_inspect WHERE `status` NOT IN ( 0, 5 ) ) rel3 ON rel3.patient_id = p.id AND rel3.step = rel2.step AND rel3.`status` = rel2.`status` LEFT JOIN ( SELECT patient_id, step, continue_flup, content_type, `status` FROM tb_flup_plan WHERE `status` NOT IN ( 0, 5 ) ) rel4 ON rel4.patient_id = p.id AND rel4.step = rel3.step AND rel4.`status` = rel3.`status` INNER JOIN ( SELECT number, `name` FROM tb_hosp ) rel5 ON rel5.number = p.hosp_number LEFT JOIN ( SELECT area, `name` FROM tmp_tb_hosp_area ) rel6 ON rel6.`name` = rel5.`name` left join ( select hosp_number, is_auddit from tmp_tb_hosp_apply ) rel7 on rel7.hosp_number = rel5.number ) rel GROUP BY provinceName