postgresql 多个联接表和条件

4zcjmb1e 于 2023-01-30 发布在 PostgreSQL

关注(0)|答案(1)|浏览(170)

我有以下表格结构：

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

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

answers
 - id int (PK)
 - question_id int (refs questions id)
 - user_id int (refs users id)
 - created_at timestamp

我的目标是获取状态为“未决”的问题，最后一个（基于在创建的**）答案由角色为管理员的用户做出，并且用户（非管理员）的最后一个答案至少是1周前。
我提出了一个非常长的查询，大部分都有效，但问题是，如果用户的回答不是最后一个，而是至少一周前的，它也会触发。
我也可以简化我的查询，删除一堆内部连接...

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'))

postgresql

来源：https://stackoverflow.com/questions/75144682/multiple-joined-tables-and-conditions

1条答案

按热度按时间

vptzau2j1#

您两次联接答案表，但未对其进行筛选。您可以使用子查询解决此问题，如下所示：

SELECT questions.*
FROM questions
JOIN (
  SELECT question_id, MAX(created_at) AS max_created_at
  FROM answers
  WHERE user_id IN (SELECT id FROM users WHERE role = 'admin')
  GROUP BY question_id
) admin_answers ON questions.id = admin_answers.question_id
JOIN (
  SELECT question_id, MAX(created_at) AS max_created_at
  FROM answers
  WHERE user_id NOT IN (SELECT id FROM users WHERE role = 'admin')
  GROUP BY question_id
) nonadmin_answers ON questions.id = nonadmin_answers.question_id
WHERE questions.status = 'opened'
AND admin_answers.max_created_at = (SELECT MAX(created_at) FROM answers WHERE question_id = questions.id)
AND nonadmin_answers.max_created_at <= NOW() - INTERVAL '1 week'

如果不工作给予我一些样本数据的表和预期的结果，并会检查它

赞(0）回复(0）举报 2023-01-30

我来回答

postgresql 多个联接表和条件

1条答案

相关问题

热门标签

最新问答