SELECT * FROM
(SELECT id as patient_id,name,hosp_number,org_number,number,survey_time,if(sex = 1,"a","b") AS sex,birth,age,phone1,phone2,
if(gastric_cancer=0,"b","a") AS gastric_cancer,if(cancer_a = 0,"b","a") AS cancer_a,
cancer_b,if(orther_tumour=0,"b","a") AS orther_tumour,
if(tumour_a = 0,"b","a") AS tumour_a,tumour_b,step
FROM tb_patient WHERE step = 0
 AND hosp_number IN (SELECT a.number FROM (
SELECT number,name FROM shchyy4dw_geca.tb_hosp
UNION
SELECT number,name FROM shchyy4dw.tb_hosp
UNION
SELECT number,name FROM shchyy4dw_qg.tb_hosp) a
left JOIN
shchyy_dw.tmp_tb_hosp_area b
ON a.name=b.name
WHERE a.NAME IS NOT null and b.area in ('上海市','浙江省','江苏省','安徽省')
)
) AS a
LEFT join
(SELECT patient_id,g17,pg1,pg2,igg
FROM tb_lab_inspect
WHERE step = 0) AS b ON a.patient_id = b.patient_id
LEFT JOIN
(select patient_id,score,(case grade when 1 then "a" when 2 then "b" when 3 then "c" end) AS grade
from tb_risk_assess WHERE step = 0) AS c ON a.patient_id = c.patient_id
LEFT JOIN
(select patient_id, if(is_inspect=1,"a","b") AS is_inspect,inspect_time,
(case inspect_way when 1 then "a" when 2 then "b" when 3 then "c" end) AS inspect_way,
if(capsule=1,"a","b") AS capsule,
precise_way,if(diagnose_result=1,"a","b") AS diagnose_result,diagnose_info
from tb_gastro_inspect where step = 0) AS d ON a.patient_id = d.patient_id
LEFT JOIN
(select patient_id,continue_flup,flup_time,if(content_type,"a","b") as content_type
from tb_flup_plan WHERE step = 1) AS e ON a.patient_id = e.patient_id
#第一次随访
LEFT join
(SELECT patient_id,g17,pg1,pg2,igg
FROM tb_lab_inspect
WHERE step = 0) AS f ON a.patient_id = f.patient_id
LEFT JOIN
(select patient_id,score,(case grade when 1 then "a" when 2 then "b" when 3 then "c" end) AS grade
from tb_risk_assess WHERE step = 0) AS g ON a.patient_id = g.patient_id
LEFT JOIN
(select patient_id, if(is_inspect=1,"a","b") AS is_inspect,inspect_time,
(case inspect_way when 1 then "a" when 2 then "b" when 3 then "c" end) AS inspect_way,
if(capsule=1,"a","b") AS capsule,
precise_way,if(diagnose_result=1,"a","b") AS diagnose_result,diagnose_info
from tb_gastro_inspect where step = 0) AS h ON a.patient_id = h.patient_id
LEFT JOIN
(select patient_id,continue_flup,flup_time,if(content_type,"a","b") as content_type
from tb_flup_plan WHERE step = 1) AS i ON a.patient_id = i.patient_id
#第二次随访
LEFT join
(SELECT patient_id,g17,pg1,pg2,igg
FROM tb_lab_inspect
WHERE step = 0) AS j ON a.patient_id = j.patient_id
LEFT JOIN
(select patient_id,score,(case grade when 1 then "a" when 2 then "b" when 3 then "c" end) AS grade
from tb_risk_assess WHERE step = 0) AS k ON a.patient_id = k.patient_id
LEFT JOIN
(select patient_id, if(is_inspect=1,"a","b") AS is_inspect,inspect_time,
(case inspect_way when 1 then "a" when 2 then "b" when 3 then "c" end) AS inspect_way,
if(capsule=1,"a","b") AS capsule,
precise_way,if(diagnose_result=1,"a","b") AS diagnose_result,diagnose_info
from tb_gastro_inspect where step = 0) AS l ON a.patient_id = l.patient_id
LEFT JOIN
(select patient_id,continue_flup,flup_time,if(content_type,"a","b") as content_type
from tb_flup_plan WHERE step = 1) AS m ON a.patient_id = m.patient_id;