多表关联查询优化需求：筛选符合特定条件的questions数据

表结构 #

users
 - id int(PK)
 - role varchar(20)

questions
 - id int (PK)
 - status varchar(20)

answers
 - id int (PK)
 - question_id int (引用questions.id)
 - user_id int (引用users.id)
 - created_at timestamp

需求 #

获取满足以下所有条件的questions数据：

• status 为 'opened'
• 按 created_at 排序的最后一条 answer 由 role 为 'admin' 的用户提交
• 最后一条由非admin用户提交的 answer 距今至少1周（若无此类回答则自动满足）

现有问题 #

自行编写的查询语句存在逻辑错误：只要某问题存在一条非最后提交、且距今超过1周的非admin用户回答，就会被错误筛选出来。同时语句冗余，希望简化并去除多余内连接。

原始查询代码 #

select distinct q.* from questions q 
inner join answers a on a.question_id = q.id
inner join answers a2 on a.question_id = q.id
inner join users u ON u.id = a.user_id
WHERE q.status = 'opened' AND u.role = 'admin' 
and a.id in (select a3.id from answers a3 inner join questions q2 on q.id = a3.ticket_id inner join users u on u.id = a3.user_id where u."role" = 'admin' and a3.created_at = (select max(a3.created_at) from "answers" a3 where a3.question_id = q2.id))
and a2.id in (select a.id from "answers" a
inner join users u on u.id = a.user_id 
where u."role" = 'user'
and a.created_at < (SELECT now() - interval '1 week'))

简化后的正确查询 #

SELECT q.*
FROM questions q
-- 获取每个问题最后一条回答的用户角色
JOIN (
    SELECT 
        a.question_id,
        u.role AS last_answer_role
    FROM answers a
    JOIN users u ON a.user_id = u.id
    WHERE (a.question_id, a.created_at) = (
        SELECT question_id, MAX(created_at)
        FROM answers
        WHERE question_id = a.question_id
        GROUP BY question_id
    )
) last_ans ON q.id = last_ans.question_id
-- 获取每个问题最后一条非admin回答的时间
LEFT JOIN (
    SELECT 
        question_id,
        MAX(created_at) AS last_non_admin_answer_time
    FROM answers a
    JOIN users u ON a.user_id = u.id
    WHERE u.role != 'admin'
    GROUP BY question_id
) non_admin_last ON q.id = non_admin_last.question_id
WHERE q.status = 'opened'
  AND last_ans.last_answer_role = 'admin'
  AND (
      non_admin_last.last_non_admin_answer_time IS NULL 
      OR non_admin_last.last_non_admin_answer_time <= NOW() - INTERVAL '1 week'
  );

逻辑说明 #

1. 子查询last_ans：精准定位每个问题的最后一条回答，获取其提交用户的角色，确保最后回答来自admin。
2. 子查询non_admin_last：统计每个问题最后一条非admin用户提交的回答时间，若无此类回答则返回NULL。
3. 主查询筛选状态为opened的问题，同时满足：最后回答是admin提交，且要么没有非admin回答，要么最后一条非admin回答已超过1周。
4. 去除了原查询中的冗余连接和DISTINCT，逻辑更严谨，避免了误判情况。

内容的提问来源于stack exchange，提问作者Hienz