如何限制内部查询中列的特定值返回的记录数？
如何限制内部查询中列的特定值返回的记录数？
提问于 2018-08-12 03:52:55
我正在查询postgres数据库，以获取用户参与的每个会话的5条消息，查询如下
select conversation.id, message from conversation inner join
(select conversation.id as conversationId, conversation_reply.message
from conversation_reply
inner join conversation on conversation.id = conversation_reply.c_id
where conversation.user_one=22 or conversation.user_two=22 order by
conversation_reply.time DESC limit 5) as messages on
messages.conversationid = conversation.id这将产生以下结果
80 jay%3A%20hello
80 jay%3A%20hey
80 jay%3A%20do%20this%20too
80 jay%3A%20throw%20please
80 jay%3A%20should%20be%20thrown这些都是id为22的会话用户所参与的所有消息，现在我想限制每个会话只有5条消息，以便
80 80msg1
80 80msg2
80 80msg3
80 80msg4
80 80msg5
73 73msg1
73 73msg2
73 73msg3
73 73msg4
73 73msg5
72 72msg1
72 72msg2
72 72msg3通过这种方式，我只有5条消息，用户22的每个会话，我怎么做呢？limit如何对内部查询起作用？
回答 1
Stack Overflow用户
回答已采纳
发布于 2018-08-12 04:05:59
我认为横向连接是解决这个问题的最好方法：
SELECT c.id as conversationId,
cr.message
FROM conversation AS c
CROSS JOIN LATERAL
(SELECT message
FROM conversation_reply
WHERE c.id = conversation_reply.c_id
ORDER BY conversation_reply.time DESC
LIMIT 5) AS cr
WHERE c.user_one = 22
OR c.user_two = 22;在LATERAL中，您可以在联接的右侧使用来自conversation的属性。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/51803214
复制相关文章
相似问题