联合限制结果
问题描述:
我想限制横向连接的结果,以便它只返回N个最近的匹配。联合限制结果
这个我的查询,但里面加入了极限似乎没有工作,因为它返回我敢肯定,问题是ame
所有游客
select am.id, am.title, ame.event, array_agg(row_to_json(visitors))
from auto_messages am
left join apps a on am.app_id = a.id
left join app_users au on a.id = au.app_id
left join auto_message_events ame on ame.auto_message_id = am.id
left join lateral (
select
id,
name,
avatar,
ame.inserted_at
from visitors v
where v.id = ame.visitor_id
order by ame.inserted_at desc
limit 1
) as visitors on visitors.id = ame.visitor_id
where am.id = '100'
group by am.id, ame.event
答
。这是行生成的地方。 join
至visitors
仅提取附加信息。
所以,这可能会解决你的问题:
select am.id, am.title, visitors.event, array_agg(row_to_json(visitors))
from auto_messages am left join
apps a
on am.app_id = a.id left join
app_users au
on a.id = au.app_id left join lateral
(select v.id, v.name, v.avatar,
ame.event, ame.inserted_at, ame.auto_message_id
from auto_message_events ame join
visitors v
on v.id = ame.visitor_id
order by ame.inserted_at desc
limit 1
) visitors
on visitors.auto_message_id = am.id
where am.id = '100'
group by am.id, visitors.event;
你也可能要改变你的select
条款,如果只想列的子集。