从SQLITE数据库中获取与最后一条消息的接触

Question

自从我使用SQL查询以来已经有一段时间了。今天，我遇到了一个问题，即从另一个表格中与参考记录连接的数据获取数据。我需要支持有效的SQL查询，该查询将与SQLITE数据库一起使用。

问题

SQLITE数据库中给出以下表的

CREATE TABLE IF NOT EXISTS "contacts" (
  "id" integer PRIMARY KEY AUTOINCREMENT NOT NULL,
  "name" text NOT NULL
);

CREATE TABLE IF NOT EXISTS "messages" (
  "id" integer PRIMARY KEY AUTOINCREMENT NOT NULL,
  "date" integer NOT NULL,
  "senderId" integer NOT NULL,
  "recipientId" integer NOT NULL,
  "text" text NOT NULL
);

： where：

• Message.SenderID References contacts.id
• message.recipientId References> References contacts.ids.ids.ids.ids.ids.id

有关提供的ID的联系，我想获取所有其他联系人，并从该联系人发送或从该联系人中收到的最新消息。结果应在消息日期下订购。

例如

，如果我们具有以下联系人：

+----+------+
| id | name |
+----+------+
| 1  | A    |
+----+------+
| 2  | B    |
+----+------+
| 3  | C    |
+----+------+

以及以下消息：

+----+------+----------+-------------+----------+
| id | date | senderId | recipientId | text     |
+----+------+----------+-------------+----------+
| 1  | 10   | 1        | 2           | A→B @ 10 |
+----+------+----------+-------------+----------+
| 2  | 20   | 2        | 1           | B→A @ 20 |
+----+------+----------+-------------+----------+
| 3  | 30   | 1        | 2           | A→B @ 30 |
+----+------+----------+-------------+----------+
| 4  | 40   | 1        | 3           | A→C @ 40 |
+----+------+----------+-------------+----------+
| 5  | 50   | 3        | 1           | C→A @ 50 |
+----+------+----------+-------------+----------+
| 6  | 60   | 2        | 3           | B→C @ 60 |
+----+------+----------+-------------+----------+
| 7  | 70   | 3        | 2           | C→B @ 70 |
+----+------+----------+-------------+----------+

联系“ A”的获取结果应该像这样：

+---------------+---------------+---------------+
| contacts.name | messages.date | messages.text |
+---------------+---------------+---------------+
| C             | 50            | C→A @ 50      |
+---------------+---------------+---------------+
| B             | 30            | A→B @ 30      |
+---------------+---------------+---------------+

相同的获取，但是对于联系人“ B”，则应导致以下输出：

+---------------+---------------+---------------+
| contacts.name | messages.date | messages.text |
+---------------+---------------+---------------+
| C             | 70            | C→B @ 70      |
+---------------+---------------+---------------+
| A             | 30            | A→B @ 30      |
+---------------+---------------+---------------+

对于联系“ C”，我们应该得到：

+---------------+---------------+---------------+
| contacts.name | messages.date | messages.text |
+---------------+---------------+---------------+
| B             | 70            | C→B @ 70      |
+---------------+---------------+---------------+
| A             | 50            | C→A @ 50      |
+---------------+---------------+---------------+

诚实地尝试失败

，我感到迷失了，我不确定我的工作是否接近正确的解决方案，但这是我尝试过的SQL查询使用：

WITH "lastMessage" AS (
  SELECT *, MAX("date") 
  FROM "messages" 
  GROUP BY "senderId" OR "recipientId"
) 
SELECT "contacts"."name", "lastMessage"."date", "lastMessage"."text"
FROM "contacts" 
JOIN "lastMessage" 
ON ("lastMessage"."senderId" = "contacts"."id") 
OR ("lastMessage"."recipientId" = "contacts"."id") 
WHERE "contacts"."id" != 1
ORDER BY "lastMessage"."date" DESC

不幸的是，它不起作用。

任何反馈都将不胜感激！

Answer 1

加入消息 to contacts和汇总的副本：

SELECT c2.name, MAX(m.date) last_date, m.text
FROM messages m 
INNER JOIN contacts c1 ON c1.id IN (m.senderId, m.recipientId)
INNER JOIN contacts c2 ON c2.id IN (m.senderId, m.recipientId) AND c1.id <> c2.id
WHERE c1.name = ?
GROUP BY c2.id
ORDER BY last_date DESC;

更改？到您想要的联系人的名称。

请参阅