sql嵌套查询优化

1.嵌套查询优化
优化前

SELECT
    q.id,
    q.title,
    q.question,
    q.person_name,
    q.department_name,
    r.response,
    r.create_time response_time
FROM
    tb_ent_question q
LEFT JOIN tb_ent_response r ON q.id = r.question_id
WHERE
 r.create_time = (
    SELECT
        MAX(create_time)
    FROM
        tb_ent_response r1
    WHERE
        r1.question_id = q.id
)
OR r.create_time IS NULL
ORDER BY
    q.create_time DESC

优化后

SELECT
    q.id,
    q.title,
    q.question,
    q.person_name,
    q.department_name,
    r.response,
    r.create_time response_time
FROM
    tb_ent_question q
LEFT JOIN tb_ent_response r ON q.id = r.question_id,
 (
    SELECT
        r1.id,
        r1.question_id,
        max(r1.create_time) AS create_time
    FROM
        tb_ent_response r1
    GROUP BY
        id
) a
WHERE
(
    a.id = r.id
    OR (r.create_time IS NULL)
)
GROUP BY
    q.id
ORDER BY
    q.create_time DESC