更新时间:2023-02-05 10:29:58
我认为您想要的是第一次就诊和最后一次糖尿病评估.我假设您所有表中的第一个字段是一个auto_increment字段,并且小提琴中的Consultation_id输入错误.
I think what you want is the first visit and last diabetes_assessment. I have assumed that the first field in all your tables is an auto_increment field and that consultation_id in the fiddle is incorrectly typed.
鉴于以上内容
MariaDB [sandbox]> select p.patient_name_en,v.*,c.diagnosis_id,d.diagnosis_name,da.date_of_assessment,da.assessment_result
-> from visit v
-> join patient p on p.patient_id = v.patient_id
-> join consultation c on c.patient_id = v.patient_id and c.visit_id = v.visit_id
-> join diagnosis d on d.diagnosis_id = c.diagnosis_id
-> left join
-> (
-> select da.patient_id, da.date_of_assessment,da.assessment_result
-> from diabetes_assessment da
-> where da.diabetes_assessment_id = (select max(da1.diabetes_assessment_id) from diabetes_assessment da1 where da1.patient_id = da.patient_id)
-> ) da on da.patient_id = v.patient_id
-> where v.visit_id = (select min(visit_id) from consultation c where c.patient_id = v.patient_id)
-> and c.diagnosis_id in (1,2)
-> and v.clinic_id = 361
-> ;
+-----------------+----------+------------+-----------+---------------+--------------+--------------+---------------------------------------------+--------------------+-------------------+
| patient_name_en | visit_id | patient_id | clinic_id | date_of_visit | visit_status | diagnosis_id | diagnosis_name | date_of_assessment | assessment_result |
+-----------------+----------+------------+-----------+---------------+--------------+--------------+---------------------------------------------+--------------------+-------------------+
| ABC | 1 | 361-9001 | 361 | 2017-03-03 | Active | 1 | Diabetes mellitus with diabetic nephropathy | 2017-05-05 | 40.00 |
| XYZ | 3 | 361-0361 | 361 | 2017-10-03 | Active | 2 | E01 Diabetes mellitus with kidney disease | 2017-03-10 | 30.50 |
+-----------------+----------+------------+-----------+---------------+--------------+--------------+---------------------------------------------+--------------------+-------------------+
2 rows in set (0.00 sec)