SQL-需要查询以获取用户所属的每个线程中的最新消息 [英] SQL - need a query to get most recent message in each thread that a user is part of

问题描述

在我的应用中，消息线程"定义为两个或多个用户之间的所有消息，并且不嵌套.它需要像Facebook消息一样工作.

In my app, a "message thread" is defined as all messages between two or more users, and is not nested. It needs to work like Facebook messages.

我需要一个查询，该查询生成用户为成员的所有消息线程"的列表，并按具有最近活动的线程降序排列.结果是一个包含不同线程的表，其中每行包含threadID，postDate和messageBody.

I need a query that generates a list of all "message threads" in which a user is a member, sorted by threads with the most recent activity, descending. The result is a table of distinct threads, where each row contains the threadID, postDate and messageBody.

这是我的模式:

MessageThreads (线程ID，lastPostDate)
MessageThreadUsers (threadFK，userFK)
消息(消息ID，threadFK，senderFK，postDate，messageBody)
用户(用户ID，用户名，用户电子邮件...)

MessageThreads (threadID, lastPostDate)
MessageThreadUsers (threadFK, userFK)
Messages (messageID, threadFK, senderFK, postDate, messageBody)
Users (userID, userName, userEmail, ...)

首先，此查询为我提供了用户所在的所有线程中的所有消息:

To start with, this query gives me all messages from all threads that the user is in:

SELECT * FROM MessageThreadUsers
JOIN Messages ON MessageThreadUsers.threadFK = Messages.threadFK
WHERE MessageThreadUsers.userFK = 'usr_developer'
ORDER BY messageDate DESC

但是我怎么只得到最新的呢?我想我会使用MAX(messageDate)函数，但是如何在这样的JOIN中工作呢?以及如何为每个线程提取包含消息数据的一行?

But how would I get only the most recent? I think I would use the MAX(messageDate) function, but how does that work in a JOIN like this? And how would I pull a single row with message data for each thread?

如果您可以在TSQL中发布答案，将会有很多帮助，但我们将提供任何帮助.谢谢！

It would help quite a bit if you can post your answer in TSQL, but any help is appreciated. Thank you!

推荐答案

事实证明，这并不像我最初想象的那么困难.因为最新的发布日期存储在线程中，所以我不必在Messages表中的messageDate上进行汇总.这是我的查询:

This turned out to be not as difficult as I first thought. Because the most recent post date is being stored in the thread, I don't have to aggregate on the messageDate in the Messages table. Here's my query:

SELECT DISTINCT 
    MessageThreadUsers.threadFK, 
    MessageThreads.threadDate, 
    [Messages].MessageBody, 
    [Messages].senderFK,
    Users.userFullName
FROM MessageThreadUsers
JOIN MessageThreads ON MessageThreadUsers.threadFK = MessageThreads.threadID
JOIN Messages ON MessageThreads.threadDate = Messages.messageDate
JOIN Users ON Messages.senderFK = Users.userID
WHERE userFK = 'usr_developer'

这篇关于SQL-需要查询以获取用户所属的每个线程中的最新消息的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！