MySQL 获取按上次消息发送时间排序的团队聊天

Question

我有 2 个表，负责团队和团队消息。假设它们的结构是这样的:

teams
team_id |   team_name
--------+------------
      1 |  First team
      2 | Second team
      3 |  Third team

team_messages
team_message_id | team_id | message_text | send_time
----------------+---------+--------------+----------
              1 |       1 |              |         1
              2 |       3 |              |         2
              3 |       2 |              |         3

我想向团队展示的方式是:

team_id | team_name
--------+------------
      2 | Second Team
      3 | Third team
      1 | First team

所以基本上我需要在该团队描述中显示按最后一条消息排序的所有团队。我试过的是

SELECT * FROM teams a
ORDER BY
(
    SELECT `send_time`
    FROM team_messages b
    ORDER BY b.`t_message_id` DESC
    LIMIT 1
) DESC

但这似乎给出了错误的结果

Answer 1

您的原始查询只为所有记录选择一行，即最新消息时间。尝试类似的东西

SELECT a.*, (
    SELECT max(send_time)
    FROM team_messages b
    WHERE b.team_id = a.team_id
    ) as ord
FROM teams a
ORDER BY ord DESC

如果 MySQL 不允许按以下方式使用别名，您可能需要将其移动到派生表中:

SELECT * FROM (
   SELECT a.*, (
     SELECT max(send_time)
     FROM team_messages b
     WHERE b.team_id = a.team_id
     ) as ord
   FROM teams a
)
ORDER BY ord DESC