...两个用户之间对话中的最新消息。
假设用户有ID1
and 3
,就像您在小提琴中所做的那样,我们对最新消息感兴趣created_at
and (sender_id, receiver_id)
being (1,3)
or (3,1)
.
您可以使用临时行类型来简化语法:
SELECT *
FROM messages
WHERE (sender_id, receiver_id) IN ((1,3), (3,1))
ORDER BY created_at DESC
LIMIT 1;
或者显式地(稍微快一点,也更容易与索引一起使用):
SELECT *
FROM messages
WHERE (sender_id = 1 AND receiver_id = 3 OR
sender_id = 3 AND receiver_id = 1)
ORDER BY created_at DESC
LIMIT 1;
For all用户的对话
根据评论中的要求添加了解决方案。
SELECT DISTINCT ON (user_id) *
FROM (
SELECT 'out' AS type, id, receiver_id AS user_id, body, created_at
FROM messages
WHERE sender_id = 1
UNION ALL
SELECT 'in' AS type, id, sender_id AS user_id, body, created_at
FROM messages
WHERE receiver_id = 1
) sub
ORDER BY user_id, created_at DESC;
这里的方法是将外部发送者/接收者折叠成一列以简化最后一行的提取。
详细解释DISTINCT ON
在这个相关的答案中:
- 选择每个 GROUP BY 组中的第一行? https://stackoverflow.com/questions/3800551/select-first-row-in-each-group-by-group/7630564#7630564
sqlfiddle http://sqlfiddle.com/#!15/1d80e/1- 改进和简化的测试用例