MySql 只选择来自不同线程的最新消息，按时间戳排序 - 类似于 Facebooks 的私有(private)消息收件箱(2013)

Question

尝试重新创建类似于 Facebook 这些天设置的私有(private)消息系统。我有问题的部分是只选择来自按时间戳排序的不同线程的最新消息。

这是迄今为止我能想到的最接近的查询。问题是收件箱应该只为每个线程返回一行，目前我的查询从同一线程返回多行:

SELECT m.created_on, m.thread_id, m.message_id, m.created_by, m.body, u.first_name, u.last_name, u.thumb_img

FROM pms_messages AS m, pms_recips AS r, users AS u

WHERE r.uid =19

AND r.status

IN ('A', 'N'
)

AND r.thread_id = m.thread_id

AND (SELECT max(r.message_id)) = m.message_id 

AND m.created_by = u.uid

ORDER BY created_on DESC

这是我当前数据库设置的 img，如果它可以简化我的查询并完成工作，我愿意对我的表进行更改。再往下是对我的表格/收件箱规范的更详细解释。

更具体地说:

*任何两个用户之间的消息都发生在一个正在进行的线程中。任何 2 个用户之间的所有消息都只是一个正在进行的对话(线程)的延续，即使两个用户都删除了彼此之间所有以前的消息，任何 future 的消息仍将出现在同一个线程上。

*个别消息由 message_id 跟踪。

*每个主题在任何给定时间仅在收件箱中显示一次，并将显示该主题的最新消息。

*接收和发送的消息将显示在同一个收件箱中，而不是收件箱和发件箱。

因此，如果我是用户 A，我有一条消息我还没有从用户 B 那里读到 - 20 分钟前，我回复了用户 C 昨天 - 10 分钟前发给我的消息和用户 D 的另一条消息 - 2 分钟前我的收件箱应如下所示:

用户 D 的线程 - 显示用户 D 发送给我的消息。

---

用户 C 的线程 - 显示我发送给用户 C 的消息

---

用户 B 的线程 - 显示用户 B 发送给我的消息

---

*显示的线程将按最近到最旧的顺序排序。

Answer 1

JOIN 与表 pms_messages 具有以下内容:

SELECT thread_id, MAX(Created_ON) AS NewestDate
FROM messages 
GROUP BY thread_id

然后它将删除除具有 MAX(Created_ON) 的最新消息之外的所有消息。像这样:

SELECT 
  m.created_on, 
  m.thread_id, 
  m.message_id, 
  m.created_by, 
  m.body, 
  u.first_name, 
  u.last_name, 
  u.thumb_img
FROM pms_messages AS m
INNER JOIN
(
   SELECT thread_id, MAX(Created_ON) AS NewestDate
   FROM messages 
   GROUP BY thread_id
) AS m2  ON m.created_on = m2.Newestdate
        AND m.thread_id  = m2.thread_id
INNER JOIN pms_recips AS r ON r.thread_id  = m.thread_id
INNER JOIN users      AS u ON m.created_by = u.uid
WHERE r.uid =19
  AND r.status IN ('A', 'N')
ORDER BY created_on DESC;

---

更新1

将r.message_id = m.message_id添加到两个表pms_messages、pms_recips之间的JOIN条件中.像这样:

SELECT 
  m.created_on, 
  m.thread_id, 
  m.message_id, 
  m.created_by, 
  m.body, 
  u.first_name, 
  u.last_name, 
  u.thumb_img
FROM pms_messages AS m
INNER JOIN
(
   SELECT thread_id, MAX(Created_ON) AS NewestDate
   FROM pms_messages
   GROUP BY thread_id
) AS m2  ON m.created_on = m2.Newestdate
        AND m.thread_id  = m2.thread_id
INNER JOIN pms_recips AS r  ON r.thread_id  = m.thread_id 
                           AND r.message_id = m.message_id
INNER JOIN users      AS u  ON m.created_by = u.uid
WHERE r.uid =19
  AND r.status IN ('A', 'N')
ORDER BY created_on DESC;

SQL Fiddle Demo