select
sp.researchId,
d.name doctorName,
p.name patientName,
h.name hospitalName,
ad3.`name` provinceName,
ad2.`name` cityName,
a.`name` districtName,
CASE WHEN h.address is NULL
THEN d.address
ELSE h.address END address,
CASE WHEN ra.audits = 0
THEN '待审核'
WHEN ra.audits = 1 THEN '审核中'
WHEN ra.audits = 3 THEN '脱落'
WHEN ra.audits = 4 THEN '中止'
WHEN ra.audits = 5 THEN '初审(不通过)'
WHEN ra.audits = 6 THEN '初审(通过)'
WHEN ra.audits = 7 THEN '审核通过'
WHEN ra.audits = 8 THEN '审核不通过'
ELSE '未知' END audits
from survey_patient sp
INNER JOIN research_audits ra on ra.research_id=sp.researchId
INNER JOIN doctor d on d.happyness_id=sp.happyness_id_doctor
INNER JOIN hospital h on h.id=d.hospital_id
INNER JOIN address a on a.id=h.district_id
INNER JOIN patient p on p.happyness_id=sp.happyness_id_patient
LEFT JOIN address ad2 on ad2.id=a.parent_id
LEFT JOIN address ad3 on ad3.id=ad2.parent_id
GROUP BY sp.researchId
sql CASE WHEN 使用
最新推荐文章于 2021-07-22 14:36:23 发布