获取每组的最后一行？

Question

你好，基本上我正在尝试将 Facebook 的消息系统复制到我的网站上。

这就是逻辑... “当 user1 创建一条新消息发送给 user7 时，会创建一个 thread_id 为 1(table: messages_thread) 的新线程，并将一个新条目插入到 table:messages 中，即 message_id 1(table:messages)。当 user7 回复时对于 user1 的消息，message2 被创建，它的 thread_id 为 1。

现在，当用户 1 创建一条新消息发送给 user7 时，线程 2 被创建，消息 3 被创建。当 user7 回复线程 2 时，消息 4 被创建（。希望你明白逻辑。）

一切都很好。唯一的问题是我需要选择线程中的最新消息，但我在 sql 方面遇到了麻烦，

我现在拥有的这个 sql...

SELECT max(message_id) message_id, m.thread_id, m.body, m.user_id,m.to_id, m.message_status, m.new, m.date, u.id, u.displayname, u.username, u.profile_img
FROM messages m INNER JOIN users u ON u.id = m.user_id
WHERE to_id = 7  AND (message_status = 'unread' or message_status='read' or message_status='saved') 
group by thread_id Order by message_id Desc LIMIT 10

产生这个...

+------------+-----------+----------------------+---------+-------+----------------+-----+------------+----+--------------+----------+-------------+
| message_id | thread_id | body                 | user_id | to_id | message_status | new | date       | id | displayname  | username | profile_img |
+------------+-----------+----------------------+---------+-------+----------------+-----+------------+----+--------------+----------+-------------+
|          6 |         2 | Really nice          |       1 |     7 | read           |   0 | 1298617367 |  1 | Kenny  Blake | imkenee  | 28_1        |
|          4 |         1 | Whats good with you? |       1 |     7 | read           |   0 | 1298607438 |  1 | Kenny  Blake | imkenee  | 28_1        |
+------------+-----------+----------------------+---------+-------+----------------+-----+------------+----+--------------+----------+-------------+

最新行（最后一行），

我该怎么做？谢谢！

我试图选择每个组中的

+----+---------+----------------+-------------+-----------+---------------+-------------+------------+
| id | user_id | subject        | from_status | to_status | from_s_delete | to_s_delete | date       |
+----+---------+----------------+-------------+-----------+---------------+-------------+------------+
|  1 |       1 | Hey Kenny      | unread      | unread    |             0 |           0 | 1298607438 |
|  2 |       7 | Check out this | unread      | unread    |             0 |           0 | 1298617344 |
+----+---------+----------------+-------------+-----------+---------------+-------------+------------+

这很好，但有一个小问题，它选择每个组中的第一行，

+------------+-----------+---------+-------+-----------------------------------------------------------+----------------+-----------------+-----+------------+
| message_id | thread_id | user_id | to_id | body                                                      | message_status | is_sent_deleted | new | date       |
+------------+-----------+---------+-------+-----------------------------------------------------------+----------------+-----------------+-----+------------+
|          1 |         1 |       1 |     7 | Whats good with you?                                      | read           |               0 |   0 | 1298607438 |
|          2 |         1 |       7 |     1 | Nothing Kenny just chilling. Whats up with you though???? | read           |               0 |   0 | 1298607473 |
|          4 |         1 |       1 |     7 | Just posted victor how are you man?                       | read           |               0 |   0 | 1298607956 |
|          5 |         2 |       7 |     1 | Look at this poem....                                     | read           |               0 |   0 | 1298617344 |
|          6 |         2 |       1 |     7 | Really nice                                               | read           |               0 |   0 | 1298617367 |
|          7 |         2 |       7 |     1 | Yea i know right :)                                       | unread         |               0 |   0 | 1298617383 |
+------------+-----------+---------+-------+-----------------------------------------------------------+----------------+-----------------+-----+------------+

Answer 1

将线程分组到子查询中，这将返回每个线程的最后一条消息：

SELECT m.message_id, m.thread_id, m.body, m.user_id,
    m.to_id, m.message_status, m.new, m.date, u.id, u.displayname, u.username, u.profile_img
FROM messages m 
  INNER JOIN users u ON u.id = m.user_id
  INNER JOIN (
    SELECT MAX(message_id) MaxMsgIDForThread
    FROM messages
    WHERE to_id = 7
      AND (message_status = 'unread'
        or message_status='read'
        or message_status='saved') 
    GROUP BY thread_id
  ) g ON m.message_id = g.MaxMsgIDForThread
Order by m.message_id Desc
LIMIT 10

WHERE 可能需要移动到外部查询，现在它将选择满足条件的最后一条消息，移动如果您想在不满足条件的情况下完全跳过线程，则将其传递到外部查询。

您还应该考虑将消息状态存储为 ENUM 这将有助于比较。

Answer 2

无法向分组添加顺序
但也许这有效：

SELECT max(message_id) message_id, MAX(m.thread_id), m.body, m.user_id,m.to_id, m.message_status, m.new, m.date, u.id, u.displayname, u.username, u.profile_img
FROM messages m INNER JOIN users u ON u.id = m.user_id
WHERE to_id = 7  AND (message_status = 'unread' or message_status='read' or message_status='saved') 
group by thread_id Order by message_id Desc LIMIT 10