用CTE编写SQL请求并计算其行数

Question

我有两张桌子：Contacts和Messages。我想获取一个不属于任何表的聊天结构(换句话说:没有Chats表，我只想构建一个查询)，这个查询应该包含：

• 我指的是contact在那次聊天中
• 我和那个联系人之间的一个lastMessage (如果我没有最后一条消息，那次联系-我不应该得到具体的结果)
• unreadCount，它告诉对话中有多少条消息还没有被读取。

我的桌子是：

1. 联系人

• uniqueId (Blob)
• username (文本)

1. 消息

• isRead (Bool)
• sender (Blob)
• receiver (Blob)
• 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，反之亦然)。

当前结果的问题是，它为每个联系人(考虑到每个联系人都有一条发送和接收的消息)获取重复的聊天，并且它无法获取un。

编辑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

Answer 1

使用window函数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。

见演示。

Answer 2

下面的查询将对您有帮助。

Declare @Myid int 
set @Myid = 3

select Sender.username [Sender] , receiver.username [Receiver],
Last_msg.isRead,
Last_msg.timestamp,
Last_msg.UnReadCount
From contact
cross apply  (select  top(1) * , sum(case when Messages.isRead = true then 1 else 0 end) over(order by @Myid) UnReadCount
                                            from Messages 
                                                where (contact.uniqueId = Messages.receiver and @Myid = Messages.sender) or 
                                                (contact.uniqueId = Messages.sender and @Myid = Messages.receiver)
                                                order by timestamp desc) Last_msg
join contact Sender on
Sender.uniqueId = Last_msg.sender
join contact receiver on
receiver.uniqueId = Last_msg.receiver

在这里，您只需要确保isRead的数据类型。我已经用0/1测试过了，但是在你的例子中，正确和错误，我已经改变了它，你只需要确保。Messages.isRead = true，如果你有任何问题，只需评论我，谢谢。

​

​