Запрос MySQL с выбором последней необходимой даты, но с другим значением столбца, которое вообще не связано с этой датой

Question

У меня есть этот запрос SQL:

SELECT 
t1.patient_id,
CONVERT(aes_decrypt(t4.patient_name_en, 'k') 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,
(SELECT 
CASE 
    WHEN 
        (period_diff(EXTRACT(YEAR_MONTH FROM now()), EXTRACT(YEAR_MONTH FROM date_of_assessment))>=6) 
    THEN 
        'Yes' 
    ELSE 
        'No'  
    END 
FROM 
    diabetes_assessment t5 
    where t5.patient_id = t1.patient_id LIMIT 1) as assessment_needed
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
ORDER BY 
    t5.date_of_assessment DESC

Где мне нужно выбрать разные столбцы, и большинство из них являются date_of_assessment, assessment_result и case результат.

Вот SQL Fiddle об этом.

Проблема с результатом этих 3 важных полей. Как вы видите в этом ряду:

|   361-9001 |          (null) |    2017-03-03 | Diabetes mellitus with diabetic nephropathy |         2017-05-05 |                31 |                No |

Выбранная дата является последней датой оценки, выполненной для пациента, но результатом оценки на эту конкретную дату является 40 и 31. Таким образом, запрос возвращает правильные поля, но он просто выбирает последнюю дату с произвольным результатом оценки, который не связано с этой датой.

Вот данные оценочной таблицы:

insert into diabetes_assessment values (1, '361-9001', 361, '2017-04-04', 31);
insert into diabetes_assessment values (2, '361-9001', 361, '2017-05-05', 40);
insert into diabetes_assessment values (3, '361-0361', 361, '2017-03-10', 30.5);

Таким образом, результат должен быть взят из этой строки:

вставить в значения diabetes_assessment (2, "361-9001", 361, "2017-05-05", 40);

Я пытался добавить max() to analysis_result, но он все еще дает ошибочные несвязанные данные.

Answer 1

Предполагая, что вы используете mysql. Если вам нужны значения для неагрегированного столбца, вы должны сопоставить ваш запрос с результатом для агрегированного результата, например, в подзапросе (если вы используете sql-сервер вместо поиска tupr, вы можете использовать внутреннее объединение в том же подслете)

РЕДАКТИРОВАТЬ: Добавление пропущенных скобок

SELECT 
t1.patient_id,
CONVERT(aes_decrypt(t4.patient_name_en, 'k') USING utf8mb4) as patient_name_en,
t3.date_of_visit as date_of_visit, 
t2.diagnosis_name,
ifnull(t5.date_of_assessment, 'N/A') as date_of_assessment,
ifnull(t5.assessment_result, 0) as assessment_result,
(
    SELECT 
    CASE 
        WHEN 
            (period_diff(EXTRACT(YEAR_MONTH FROM now()), EXTRACT(YEAR_MONTH FROM date_of_assessment))>=6) 
        THEN 
            'Yes' 
        ELSE 
            'No'  
        END 
    FROM diabetes_assessment t5 
    where t5.patient_id = t1.patient_id LIMIT 1
) as assessment_needed
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'
AND ( t1.patient_id,t3.date_of_visit, ifnull(t5.date_of_assessment, 'N/A')) IN (
    SELECT 
    t1.patient_id,
    min(t3.date_of_visit) as date_of_visit, 
    max(ifnull(t5.date_of_assessment, 'N/A')) as date_of_assessment
    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
    ORDER BY t5.date_of_assessment DESC


)
ORDER BY  t5.date_of_assessment DESC

Обратите внимание, что в таблицах используется левое соединение, а условие для тех же таблиц работает как INNER JOIN