-- ----------------------------------------平台筛查综合统计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