时间戳

首页 > 解决方案 > 如何获取唯一值来比较同一张表的两列？

sql - 如何获取唯一值来比较同一张表的两列？

问题描述

目前，我正在使用 SQLite 开发一个 Django 项目，在该项目中我创建了一个消息聊天框，并且我想获取登录用户的最新消息，无论它是发送还是接收。

id   receiver_id      sender_id    message_content   created_at
1    1                 2            some text         2020-08-11 13:29:47.342944
3    3                 2            some text         2020-08-11 13:44:55.499638
4    2                 1            some text         2020-08-11 14:20:55.499638
5    1                 2            some text         2020-08-12 05:06:05.497500
6    2                 5            some text         2020-08-12 10:39:31.234082
7    4                 1            some text         2020-08-14 13:25:19.357876

使用下面的 SQL 查询后。

SELECT max(created_at), * 
FROM hireo_messages 
WHERE receiver_id=2 or sender_id=2 
GROUP BY receiver_id, sender_id 
ORDER BY created_at DESC

我得到了以下结果。

id      receiver_id   sender_id     message_content   created_at
6       2             5             some text         2020-08-12 10:39:31.234082
5       1             2             some text         2020-08-12 05:06:05.497500
4       2             1             some text         2020-08-11 14:20:55.499638
3       3             2             some text         2020-08-11 13:44:55.499638

如您所见，id 5、4 都在互相聊天。所以我想获取除 id 4 之外的所有记录，因为 2 个用户之间的最新聊天在 id 5 中。它与 Facebook Messenger 仪表板中使用的概念相同。请指导我是通过查询解决还是使用任何其他方式。在此先感谢您的帮助！

标签： sqldjangosqlitedjango-queryset

解决方案

我想你想要：

SELECT LEAST(receiver_id, sender_id), GREATEST(receiver_id, sender_id), MAX(created_at)
FROM hireo_messages
WHERE 2 IN (receiver_id, sender_id)
GROUP BY LEAST(receiver_id, sender_id), GREATEST(receiver_id, sender_id)
ORDER BY MAX(created_at) DESC;

如果您想要完整的行，请使用窗口函数：

SELECT m.*
FROM (SELECT m.*,
             ROW_NUMBER() OVER (PARTITION BY LEAST(receiver_id, sender_id), GREATEST(receiver_id, sender_id) ORDER BY created_at DESC) as seqnum
      FROM hireo_messages m
      WHERE 2 IN (receiver_id, sender_id)
     ) m
WHERE seqnum = 1
ORDER BY MAX(created_at) DESC;

推荐阅读