使用子查询按特定列分组输出相同的错误结果 [英] Using sub query to group by specific column is outputting the same erroneous result

问题描述

我问了

但我真正想要的是仅按 patient_id 分组，这样我就可以为每个早期被诊断患有糖尿病的患者获得一行数据.

我搜索了很多，发现我可以在这个查询中使用子查询.

所以我想出了以下子查询:

SELECT 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

结果按 patient_id 分组，我得到了真正想要的 2 id 而不是更多.就连ID0361的患者，每次去诊所都被诊断出患有多种类型的糖尿病，但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左连接诊断 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 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

但是我得到了与上图相同的结果，其中每个示例 patient_id=0361 显示为 4 行，但我希望显示一次，并显示第一次的日期他被诊断出患有糖尿病的时间.

解决方案

SELECT t.patient_id,CONVERT(aes_decrypt(t4.patient_name_en, 'key1') USING utf8mb4) 作为patient_name_en，记为 date_of_visit，tt2.diagnosis_name，max(ifnull(tt5.date_of_assessment, 'N/A')) 作为 date_of_assessment,ifnull(tt5.assessment_result, 0) 作为评估结果FROM 咨询 t左连接访问 tt3 ON tt3.visit_id = t.visit_id左连接诊断 tt2 ON t1.diagnosis_id = tt2.diagnosis_id内部联接(选择 t1.patient_id,min(t3.date_of_visit) 注意FROM 咨询 t1左连接诊断 t2 ON t1.diagnosis_id = t2.diagnosis_id左连接访问 t3 ON t3.visit_id = t1.visit_idWHERE t2.diagnosis_name LIKE '%Diabetes%' AND t1.clinic_id = '361'AND t3.visit_status="活动"GROUP BY t1.patient_id)INNERTABLE ON t.patient_id=INNERTABLE.patient_id 和 INNERTABLE.mindate左加入患者 t4 ON t4.patient_id = tt3.patient_id左加入糖尿病评估 t5 ON t5.patient_id = t4.patient_id

试试上面的查询.

I asked this question few days ago on SO, but with no valid answer for it.

The problem was that the following 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, 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 

Is giving the following result:

But what I really want is to group by only patient_id so I can get only one row per one patient who was diagnosed earlier with Diabetes.

I searched a lot and found that I can use sub query inside this query.

So I came up with the following sub query:

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

The result was grouped by patient_id, and I got the 2 id that really want and not more. Even the patient with ID 0361 was diagnosed with multiple types of diabetes in each visit to the clinic, but it's id is shown once.

Now I want to take this sub query and add it to the initial one:

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 

But I've got the same result of the image above, where per example patient_id=0361 is shown in 4 rows, but I want is to be shown once, with the date of the first time he was diagnoised with Diabetes.

解决方案

SELECT t.patient_id,
CONVERT(aes_decrypt(t4.patient_name_en, 'key1') USING utf8mb4) as patient_name_en,
mindate as date_of_visit, 
tt2.diagnosis_name,
max(ifnull(tt5.date_of_assessment, 'N/A')) as date_of_assessment,
ifnull(tt5.assessment_result, 0) as assessment_result 
FROM consultation t
LEFT JOIN visit tt3 ON tt3.visit_id = t.visit_id
LEFT JOIN diagnosis tt2 ON t1.diagnosis_id = tt2.diagnosis_id
INNER JOIN
(
    SELECT t1.patient_id,min(t3.date_of_visit) mindate
    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 
    WHERE t2.diagnosis_name LIKE '%Diabetes%' AND t1.clinic_id = '361'
    AND t3.visit_status="Active"
    GROUP BY t1.patient_id
)INNERTABLE ON t.patient_id=INNERTABLE.patient_id and INNERTABLE.mindate
LEFT JOIN patient t4 ON t4.patient_id = tt3.patient_id
LEFT JOIN diabetes_assessment t5 ON t5.patient_id = t4.patient_id

Try above query.

这篇关于使用子查询按特定列分组输出相同的错误结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！