postgresql Postgres WITH子查询使用id关联

t98cgbkg 于 2023-04-05 发布在 PostgreSQL

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

我知道，这个查询（它是不正确的，只是一个例子）可以用其他方式编写。但是，我有兴趣在有这样的关系m.user_id = u.id时使用WITH。这可能吗？https://www.postgresql.org/docs/current/queries-with.html-看起来WITH只用于准备好的数据，而不是动态的。

with last_message as (
  select m.message 
  from messages m 
  where m.user_id = u.id 
  order by m.id desc 
  limit 1
)
select u.*, last_message
from users u
where last_message = 'some_message';

P.S.不明白，为什么Postgres限制我们有子查询变量（作为将被执行的语句），所以我们可以很容易地在select和where子句中使用这个变量。目前，我必须从select子句复制子查询并在where子句中使用它（

postgresql

来源：https://stackoverflow.com/questions/75884581/postgres-with-subquery-using-relation-by-id

1条答案

按热度按时间

qjp7pelc1#

如果我理解正确的话，您的 problematic 查询-在WHERE子句中带有子查询 copied，类似于下面的查询

select u.*, m.last_message
from users u
cross join lateral (
  select  m.message last_message
  from messages m 
  where m.user_id = u.id 
  order by m.id desc 
  limit 1
) m  
where (
  select m.message 
  from messages m 
  where m.user_id = u.id 
  order by m.id desc 
  limit 1
) = 'some_message';

这确实不是首选的解决方案，不仅违反了DRY原则，而且子查询可能会为每个用户执行两次。
不幸的是（或者更好的是不出意料地），您不能像您的示例中那样对其他表使用WITH子查询 correlated。

ERROR: missing FROM-clause entry for table "u"

另一种选择是什么？定义CTE，使其为所有用户生成 last message，并将其与查询连接。

with last_message as (
  select distinct on (m.user_id) user_id, m.message last_message 
  from messages m 
  order by m.user_id, m.id desc 
)
select u.*, m.last_message
from users u
join last_message m on u.id = m.user_id
where m.last_message = 'some_message';

作为另一种选择，您可以定义一个视图来过滤 * 最后一条消息 *，并在连接中使用它

create view last_message as
  select distinct on (m.user_id) user_id, m.message last_message 
  from messages m 
  order by m.user_id, m.id desc;
 
select u.*, m.last_message
from users u
join last_message m on u.id = m.user_id
where m.last_message = 'some_message';

赞(0）回复(0）举报 2023-04-05

我来回答

postgresql Postgres WITH子查询使用id关联

1条答案

相关问题

热门标签

最新问答