我有兩張桌子：Contacts和Messages。我想獲取一個不屬于任何表的 Chat 結構（換句話說：沒有Chats表我只想構建一個查詢）這個查詢應該包含：
contact我在那個聊天中指的是- 我和那個聯系人之間的一個
lastMessage（如果我沒有與那個聯系人的最后一條訊息 - 我應該不會從那個聯系人那里得到任何結果） unreadCount這會告訴您該對話中有多少訊息尚未閱讀。
我的桌子是：
- 聯系人
uniqueId（斑點）username（文本）
- 留言
isRead（布爾）sender（斑點）receiver（斑點）timestamp（整數）
我得到的最遠的是：
WITH
"lastMessage" AS (
SELECT *, MAX("timestamp")
FROM "messages"
GROUP BY "sender", "receiver"
),
"unreadCount" AS (
SELECT COUNT(*)
FROM "messages" WHERE "isRead" = 0
GROUP BY "sender"
)
SELECT "contacts".*, "unreadCount".*, "lastMessage".*
FROM "contacts"
JOIN "lastMessage"
ON ("lastMessage"."sender" = "contacts"."uniqueId")
OR ("lastMessage"."receiver" = "contacts"."uniqueId")
LEFT JOIN "unreadCount"
ON "unreadCount"."sender" = "contacts"."uniqueId"
ORDER BY "lastMessage"."timestamp" DESC
PS：訊息的發送者/接收者等于一個聯系人的uniqueId（或者我自己的uniqueId，反之亦然）
我遇到的當前結果的問題是它為每個聯系人獲取重復的聊天（考慮到每個聯系人都有發送和接收的訊息）并且它無法獲取未讀訊息
編輯1：這就是我要找的...
我有我的資料庫：
Contacts table:
- username: John Doe,
uniqueId: (Blob...)
- username: Alice Shrek,
uniqueId: (Blob...)
Messages table:
- sender: (Blob from my uniqueId),
receiver: (Blob from John Doe),
isRead: true,
timestamp: ...1
- sender: (Blob from John Doe),
receiver: (Blob from my uniqueId),
isRead: false,
timestamp: ...2
- sender: (Blob from my uniqueId),
receiver: (Blob from Alice Shrek),
isRead: true,
timestamp: ...3
- sender: (Blob from Alice Shrek),
receiver: (Blob from my uniqueId),
isRead: false,
timestamp: ...4
（換句話說，這個假設場景我有兩個聯系人和兩條訊息，每個聯系人都有兩條訊息。我發送的一條訊息是已讀訊息，而我收到的一條訊息是未讀訊息）
我希望我的查詢獲取這些：
- username: John Doe,
uniqueId: (Blob from John doe),
sender: (Blob from John doe)
receiver: (Blob from my uniqueId)
timestamp: ...2
unreadCount: 1
- username: Alice Shrek,
uniqueId: (Blob from Alice Shrek),
sender: (Blob from Alice Shrek)
receiver: (Blob from my uniqueId)
timestamp: ...4
unreadCount: 1
uj5u.com熱心網友回復：
使用視窗函式ROW_NUMBER()識別最后一條訊息并TOTAL()獲取每個聯系人的未讀訊息總數：
WITH
person(uniqueId) AS (VALUES (?)),
aggregates AS (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY MIN(m.sender, m.receiver), MAX(m.sender, m.receiver) ORDER BY timestamp DESC) rn,
TOTAL(NOT m.isRead) OVER (PARTITION BY MIN(m.sender, m.receiver), MAX(m.sender, m.receiver)) unreadCount
FROM Messages m INNER JOIN person p
ON p.uniqueId IN (m.sender, m.receiver)
)
SELECT c.username, c.uniqueId,
a.sender, a.receiver, a.timestamp, a.unreadCount
FROM aggregates a CROSS JOIN person p
INNER JOIN Contacts c
ON c.uniqueId = CASE WHEN a.sender = p.uniqueId THEN a.receiver ELSE a.sender END
WHERE a.rn = 1;
?在 cte中替換person為您的uniqueId.
請參閱
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/432595.html
上一篇：FileNotFoundError，當我運行shutil.copy時，1個特定的“.dylib”檔案每次都會拋出FileNotFoundError錯誤
下一篇：如何在Haskell中合并兩棵樹