如何获取按连接表字段排序的记录

Question

我有两张表：

chat:
id
name

chat_messages:
id
chat_id
user_id
message
created_at

我想要一份按最近活动排序的聊天室名称列表。换句话说，我想要获得一个聊天列表，其中第一个是具有较大created_at字段的聊天，最后一个是具有较小created_at字段的聊天。

例如：

chat table:
1 General
2 News

chat_messages:
1 | 1 | 20 | Hello everybody | 2020-10 18:00:00
1 | 1 | 23 | this is a me... | 2020-10 18:00:05
1 | 1 | 15 | another message | 2020-10 18:00:15
1 | 2 | 22 | Anybody there?  | 2020-10 17:00:00
1 | 2 | 45 | Hello?????????? | 2020-10 16:00:00

期望的结果是：'News'，'General‘

有什么帮助吗？谢谢

Answer 1

试试这个：

SELECT DISTINCT `chat`.`name`
FROM `chat` JOIN `chat_message` ON `chat`.`id` = `chat_message`.`chat_id`
ORDER BY `chat_message`.`created_at` DESC

解释：

您只希望返回聊天名称。而且每个值只需要一次(这就是DISTINCT)。

您可以对created_at字段执行ORDER BY操作，然后剩下的惟一工作就是对表执行JOIN操作。

编辑：您可以尝试/改进here。

Answer 2

为每次聊天选择last Created_at：

  SELECT m.chat_id,
         max(m.created_at) as LastActivity
    FROM chat_message as m
group by m.chat_id;

SELECT c.name,
       info.LastActivity
  FROM (  SELECT m.chat_id,
             max(m.created_at) as LastActivity
        FROM chat_message as m
    group by m.chat_id) info -- virtual table "info" from first query
  JOIN chat as c ON c.id = info.chat_id; -- add table chat to query

SELECT c.name,
       info.LastActivity,
       m2.user_id, -- Print Infos of LastActivityMessage
       m2.message
  FROM (  SELECT m.chat_id,
             max(m.created_at) as LastActivity
        FROM chat_message as m
    group by m.chat_id) info
  JOIN chat as c ON c.id = info.chat_id
  JOIN chat_message as m2 ON info.LastActivity = m2.created_at; -- Search for Message at LastActivity

在最后一个查询中，我们看到了一个问题:我们不想搜索带有日期的消息！我们在表chat_message中缺少一个合适的主键。

我建议计算一个唯一的id：

create table chat_message
(
    id int not null auto_increment primary key, -- this is the message-id which will automatically set
    chat_id int not null, -- where did the user post?
    user_id int not null, -- which user?
    message text,
    created_at datetime
);

如果我们现在像这样插入消息：

INSERT INTO chat_message (chat_id, user_id, message, created_at) 
  VALUES
  (2, 45, "Hello?", "2020-10-01 16:00:00"),
  (2 , 22 , "Anybody there?"  , "2020-10-01 17:00:00"),
  (1, 20, "Hello everybody", "2020-10-01 18:00:00"),
  (1, 23,  "this is a me...", "2020-10-01 18:00:05"),
  (1, 15, "another message", "2020-10-01 18:00:15");

自动生成..Ids：

id  chat_id user_id message         created_at
1   2       45      Hello?          2020-10-01T16:00:00Z
2   2       22      Anybody there?  2020-10-01T17:00:00Z
3   1       20      Hello everybody 2020-10-01T18:00:00Z
4   1       23      this is a me... 2020-10-01T18:00:05Z
5   1       15      another message 2020-10-01T18:00:15Z

-- Now we want to add some info about the LastActivity
SELECT c.name,
       m2.id,
       m2.created_at,
       m2.user_id,
       m2.message
  FROM (  SELECT m.chat_id,
             max(m.id) as LastActivity -- select last Id
        FROM chat_message as m
    group by m.chat_id) info
  JOIN chat as c ON c.id = info.chat_id
  JOIN chat_message as m2 ON info.LastActivity = m2.id; -- search for message with correct id