MySQL 根据第一个日期从每个诊断的患者中仅选择一行

Question

编辑

我无法仅使用patent_idgroup by，我会收到sql_mode=only_full_group_by...的错误

我有以下查询，它可以帮助我获取所有已诊断患有糖尿病的患者的列表:

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

结果是:

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

此处，0361 号患者于 2017-04-06 首次被诊断患有糖尿病。所以我们只得到该患者的这一行。

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

Answer 1

我认为您想要的是第一次就诊和最后一次糖尿病评估。我假设所有表中的第一个字段是 auto_increment 字段，并且 fiddle 中的 Advisory_id 输入不正确。

鉴于上述情况

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)