MySQL从根据第一个日期诊断出的每个患者中仅选择一行 [英] MySQL Select only one row from each patient diagnosed according the the first date

问题描述

编辑

我不能仅使用patient_id来group by，我将得到错误sql_mode=only_full_group_by...

I cannot group by only with patient_id, I will get an error of sql_mode=only_full_group_by...

我有以下查询可帮助我获取所有被诊断出患有糖尿病的患者的名单:

I have the following query which is helping me to get the list of all patient that has been diagnosed with diabetes:

SELECT t1.patient_id,
CONVERT(aes_decrypt(t4.patient_name_en, 'key1') USING utf8mb4) as patient_name_en,
t3.date_of_visit as date_of_visit, 
t2.diagnosis_name,
max(ifnull(t5.date_of_assessment, 'N/A')) as date_of_assessment,
ifnull(t5.assessment_result, 0) as assessment_result 
FROM consultation t1
LEFT JOIN diagnosis t2 ON t1.diagnosis_id = t2.diagnosis_id
LEFT JOIN visit t3 ON t3.visit_id = t1.visit_id
LEFT JOIN patient t4 ON t4.patient_id = t3.patient_id
LEFT JOIN diabetes_assessment t5 ON t5.patient_id = t4.patient_id
WHERE t2.diagnosis_name LIKE '%Diabetes%' AND t1.clinic_id = '361'
AND t3.visit_status="Active"
GROUP BY t1.patient_id, t5.date_of_assessment, t4.patient_name_en, t3.date_of_visit, t2.diagnosis_name, t5.assessment_result
ORDER BY t5.date_of_assessment DESC

结果是:

如您所见，是否多次诊断出患者患有不同类型或级别的糖尿病，将显示所有行.但是我真正想要的是他第一次被诊断出这种病，所以结果只会是这两行以蓝色突出显示:

As you see if a patient is diagnosed multiple times with different types or level of diabetes, the rows are all shown. But what I really want is the first time he get diagnosed with it, so the result would be only these 2 rows highlighted in blue:

在2017-04-06中，首先被诊断出患有糖尿病的患者0361.因此，我们仅为此患者获得这一行.

Here the patient 0361 first diagnised with diabetes in 2017-04-06. So we get only this row for this patient.

我尝试使用`min(t3.date_of_visit)，但无法正常工作.

I tried to use `min(t3.date_of_visit) but it didn't worked properly.

推荐答案

我认为您想要的是第一次就诊和最后一次糖尿病评估.我假设您所有表中的第一个字段是一个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)

这篇关于MySQL从根据第一个日期诊断出的每个患者中仅选择一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！