是否有任何方法来排序这个表的值在mysql?

Question

我有两个表一个是聊天和其他用户，我有一些user_id。我需要使用两个id发送的最后一条消息对用户id进行排序。

User table:

xyz@g 123 abc@g123pqr@g123lmn@g123

Answer 1

使用GROUP By和MAX聚合查找每个用户的适用聊天id

SELECT a.*
FROM users a 
JOIN (
SELECT receiver, MAX(id) AS id
FROM chat 
GROUP BY receiver
) c ON a.id = c.receiver
ORDER BY c.id DESC

或

SELECT a.id, MAX(a.name) AS name, MAX(a.email) AS email, MAX(a.password) AS password
FROM users a 
JOIN chat c ON a.id = c.receiver
GROUP BY a.id
ORDER BY MAX(c.id) DESC