如何使用Group by语句从表中选择最新记录

Question

msg_id     msg(messages)  sender(user id)  receiver (user id)  time (timestamp)
------------------------------------------------------------------------------
 1              hello bro             1              2                 12am
 2              hello                 2              1                 12am
 3              i disscuss something  1              2                 12:01am
 4              are you free          1              2                 12:03am

-----------------------------------------------------
**User Table**
-----------------------------------------------------
 u_id       user_name    
  1           khalid
  2           brain
  3           abdullah

SELECT sender.user_name sender, receiver.user_name receiver,
       messages.time msgtime, messages.msg msg,sender.u_id u_id,
       sender.user_name user_name FROM messages LEFT JOIN user 
       AS sender ON messages.sender = sender.u_id LEFT JOIN user
       AS receiver ON messages.receiver = receiver.u_id where 
       sender.u_id<>1 group by messages.sender ORDER BY 
       messages.msg_id desc

我想要的

我想查询显示每个发件人的最新最后消息

Answer 1

您可以在根本不分组的情况下做到这一点--只需left将消息表连接到自身，谓词是相同的发件人，然后是时间戳。如果没有稍后的时间戳，那么第二个表中的值将为null，这意味着您已经标识了最近的消息。

select s.user_name as `from`, r.user_name as `to`, m1.msg, m1.time 
  from messages m1
    left join messages m2
      on m1.time < m2.time and m1.sender = m2.sender
    inner join users s
      on m1.sender = s.u_id
    inner join users r
      on m1.receiver = r.u_id
  where m2.sender is null;

如果您绝对想使用group by，您可以首先找到每个发件人的max(time)，然后将结果连接回messages and users表，如下所示：

select s.user_name as `from`, r.user_name as `to`, m.msg, m.time
  from messages m
    inner join users s
      on m.sender = s.u_id
    inner join users r
      on m.receiver = r.u_id
    inner join (
      select sender, max(`time`) as ts
        from messages
        group by sender
    ) q on m.sender = q.sender and m.time = q.ts

这两个查询将给出相同的结果