我有一个消息传递应用程序，我需要在其中返回用户所属的所有对话以及与每个对话相关联的消息。我想限制每次对话的消息数量。
表结构如下：
用户数
| id | name | email | created_at |
|------|------|----------|------------|
| 1 | Bob | [email protected] | timestamp |
| 2 | Tom | [email protected] | timestamp |
| 3 | Mary | [email protected] | timestamp |
留言内容
| id | sender_id | conversation_id | message | created_at |
|------|-----------|------------------|---------|------------|
| 1 | 1 | 1 | text | timestamp |
| 2 | 2 | 2 | text | timestamp |
| 3 | 2 | 1 | text | timestamp |
| 4 | 3 | 3 | text | timestamp |
对话内容
| id | created_at |
|----|------------|
| 1 | timestamp |
| 2 | timestamp |
| 3 | timestamp |
对话用户
| id | user_id | conversation_id |
|----|---------|-----------------|
| 1 | 1 | 1 |
| 2 | 2 | 1 |
| 3 | 2 | 2 |
| 3 | 3 | 2 |
| 4 | 3 | 3 |
| 5 | 1 | 3 |
我想加载用户（id 1）所在的所有对话（在示例中-对话1和3）。对于每次对话，我都需要与之关联的消息，并按分组conversation_id，排序created_at ASC。我当前的查询处理：
SELECT
*
FROM
messages
WHERE
conversation_id IN (
SELECT
conversation_id
FROM
conversations_users
WHERE
user_id = 1
)
ORDER BY
conversation_id, created_at ASC;
但是，这会将大量数据粘贴到内存中。因此，我想限制每次对话的消息数量。
我已经查看过rank()，ROW_NUMBER()但是不确定如何实现它们/如果需要它们。
您确实可以使用row_number()。以下查询将为您提供给定用户每次会话的最后10条消息：
select *
from (
select
m.*,
row_number() over(
partition by cu.user_id, m.conversation_id
order by m.created_at desc
) rn
from messages m
inner join conversations_users cu
on cu.conversation_id = m.conversation_id
and cu.user_id = 1
) t
where rn <= 10
order by conversation_id, created_at desc
笔记：
我将子查询in转换为常规查询join，因为我认为这是表达需求的一种更整洁的方式
我在分区子句中添加了用户ID。因此，如果删除where对用户进行过滤的子句，则将获得每个用户对话的最后10条消息
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句