自從我使用 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
);

在哪里：

• messages.senderId參考contacts.id
• messages.recipientId參考contacts.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

不幸的是，它不起作用。

任何反饋將不勝感激！

uj5u.com熱心網友回復：

加入messages2 個副本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;

更改?為所需聯系人的姓名。

請參閱演示。

標籤：sql sqlite 加入 通过...分组 where子句

上一篇：處理空值時的SQL多連接

下一篇：MySQLConditionalForwhichJoin使用