postgre如何在SQL中获取某个用户每次对话中的最新消息

Question

我需要编写一个查询，该查询返回两个用户之间的对话中的最新消息。我已经在这个小提琴中包含了架构和我（失败的）尝试：http://sqlfiddle.com/#!15/322c3/11

我已经解决这个问题一段时间了，但每次我运行任何丑陋的查询时，一只可爱的小猫都会死去。

任何帮助将不胜感激。为小猫做。

Answer 1

。两个用户之间对话中的最新消息。

假设用户 ID 1 和 3 ，就像您在小提琴中所做的那样，我们对具有最新created_at的消息感兴趣，并且(sender_id, receiver_id)正在(1,3)或(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;

对于用户的所有对话

根据评论中的请求添加了解决方案。

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的详细说明：

• 选择每个分组依据组中的第一行？

sqlfiddle - 具有改进和简化的测试用例

Answer 2

这将提供两个用户之间的最新消息，而不考虑消息方向：

  SELECT Distinct mes.ID, sendu.Username AS Sender,
  recu.Username as Receiver, Body, maxSent as TimeSent
  FROM messages mes
  INNER JOIN 
  (
    SELECT One, Two, MAX(CREATED_AT) maxSent
    FROM
    (
      SELECT  'Sender' as type, Sender_ID AS One, receiver_id as Two,created_At
      FROM messages 
      UNION ALL
      SELECT 'Receiver' as type, receiver_id AS One, Sender_ID as Two ,created_At
      FROM messages 
    ) a
    Group By One,Two
  ) b
  ON mes.created_at = b.maxSent
  INNER JOIN users sendu
  ON sendu.ID = mes.Sender_ID
  INNER JOIN users recu
  ON recu.ID = mes.Receiver_ID

它没有将"对话"分开，但没有任何意义。也许如果您还包含消息标题或标题字段，这将是可能的。

Answer 3

SELECT * 
FROM messages 
WHERE (sender_id = 1 AND receiver_id = 2) 
   OR (sender_id = 2 AND receiver_id = 1) 
ORDER BY created_at DESC
LIMIT 1;

Answer 4

试试这个看看

SELECT  *
FROM    (SELECT * ,ROW_NUMBER() OVER(ORDER BY created_at DESC) as RowID
FROM   messages 
WHERE  (sender_id, receiver_id) IN ((1,3), (3,1))
    ) sub
WHERE   RowID = 1

Answer 5

使用此 -->从消息中按 created_at DESC 限制 1 选择 *