MySQL 查询给出错误结果，因为只有 full group by 的模式 [英] MySQL query giving erroneous results because of the mode of only full group by

问题描述

我有

新的事情是我尝试使用子查询来循环patient_id的以下查询:

SELECT t1.patient_id,CONVERT(aes_decrypt(t4.patient_name_en, 'key1') USING utf8mb4) 作为patient_name_en，min(t3.date_of_visit) 作为 date_of_visit，t2.diagnosis_name,max(ifnull(t5.date_of_assessment, 'N/A')) 作为 date_of_assessment,ifnull(t5.assessment_result, 0) 作为评估结果FROM 咨询 t1左连接诊断 t2ON t1.diagnosis_id = t2.diagnosis_idLEFT JOIN 访问 t3ON t3.visit_id = t1.visit_id左加入患者 t4ON t4.patient_id = t3.patient_idLEFT JOIN 糖尿病_评估 t5ON t5.patient_id = t4.patient_idWHERE t1.patient_id IN(选择 t1.patient_idFROM 咨询 t1左连接诊断 t2 ON t1.diagnosis_id = t2.diagnosis_id左连接访问 t3 ON t3.visit_id = t1.visit_id左加入患者 t4 ON t4.patient_id = t3.patient_id左加入糖尿病评估 t5 ON t5.patient_id = t4.patient_idWHERE t2.diagnosis_name LIKE '%Diabetes%' AND t1.clinic_id = '361'AND t3.visit_status="活动"GROUP BY t1.patient_id) ANDt2.diagnosis_name LIKE '%Diabetes%' AND t1.clinic_id = '361'AND t3.visit_status="活动"GROUP BY t1.patient_id, t5.date_of_assessment, t4.patient_name_en, t3.date_of_visit, t2.diagnosis_name, t5.assessment_resultORDER BY t5.date_of_assessment DESC

我得到了与上图相同的结果.

由于一些原因，我无法禁用 only_full_group_by 模式，因为我阅读它可能会给出一些错误的结果.

我尝试仅按小提琴中的 patient_id 分组，显然它工作正常，因为小提琴禁用了 only_full_group_by.

我该怎么办?

1. 创建一个 PHP 解决方案，我将查询分成 2 个，第一个是子查询，我将在其中获取 ID，然后在 foreach 中使用外部查询为每个查询获取一行ID 使用 LIMIT 1 ?
2. 或者禁用 only_full_group_by 模式，这会在不同方面影响我的应用程序中的其他查询?
3. 创建一个sql存储过程而不是解决方案#1?

解决方案

尝试使用 group_concat(column_name 分隔符 ', ').group_concat() 文档

这将有助于根据您想要的组连接行

I have this unanswered question where I had this erroneous result query:

SELECT
    t1.patient_id,
    CONVERT(aes_decrypt(t4.patient_name_en, :encKey) USING utf8mb4) as patient_name_en,
    min(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 :diagName AND
   t1.clinic_id = :cid AND
   t3.visit_status=:visit_status
GROUP BY
    t1.patient_id,
    t2.diagnosis_name,
    t3.date_of_visit
    t4.patient_name_en,
    t5.date_of_assessment
    t5.assessment_result
ORDER BY t5.date_of_assessment DESC 

Which gives me this result:

The new thing is that I tried the following query using sub query to loop through patient_id:

SELECT t1.patient_id,
    CONVERT(aes_decrypt(t4.patient_name_en, 'key1') USING utf8mb4) as patient_name_en,
    min(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 t1.patient_id IN 
(SELECT t1.patient_id
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) AND
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 

And I've got the same result shown at the image above.

Here is the an sql fiddle about my problem

What I really want is to show only the following two rows which means group by patient_id.

And for few reasons, I cannot disable the only_full_group_by mode,because I read it may give some faulty result.

I tried to only group by patient_id in the fiddle and apparently it works properly because the fiddle has the only_full_group_by disabled.

What should I do ?

1. Create a PHP solution where I break the query into 2, the first one is the sub query where I will get the IDs and then use the outer query in a foreach to get a row for each ID using LIMIT 1 ?
2. Or disable the only_full_group_by mode which will affect my other queries in my application on different aspects ?
3. Create an sql stored procedure instead of the solution #1 ?

解决方案

Try to use group_concat(column_name separator ', '). group_concat() doc

It will help concatenate rows based on youd desired group

这篇关于MySQL 查询给出错误结果，因为只有 full group by 的模式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！