mysql - 获取每个 friend 的所有最后一条消息 - SQL

Question

我正在尝试选择来自每个 friend 的最后一条消息，就像所有其他聊天应用程序一样。这是我目前所拥有的。

SELECT m.message, 
                   m.message_read,
                   m.message_date,
                   CASE WHEN m.sender = 4
                        THEN m.receiver
                        ELSE m.sender
                   END as friend_id,
                   CASE WHEN m.sender = 4
                        THEN p2.nickname
                        ELSE p1.nickname
                   END as name,
                   CASE WHEN m.sender = 4
                        THEN p2.image
                        ELSE p1.image
                   END as image
            FROM message as m
            JOIN profile as p1
              ON m.sender = p1.user_id    -- sender
            JOIN profile as p2 
              ON m.receiver = p2.user_id  -- receiver
            WHERE 4 IN (m.sender, m.receiver)

上面的select语句获取

+-----------+--------------+---------------------+-----------+-------+-------+
| message   | message_read | message_date        | friend_id | name  | image |
+-----------+--------------+---------------------+-----------+-------+-------+
| Hey Buddy |            1 | 2018-05-10 11:58:39 |         1 | JUAN  | NULL  |
| SUP MATE  |            1 | 2018-05-15 11:04:24 |         1 | JUAN  | NULL  |
| nooo      |            1 | 2018-05-15 10:24:36 |         2 | user3 | NULL  |
| lulz      |            1 | 2018-05-15 10:24:36 |         2 | user3 | NULL  |
| shut up   |            1 | 2018-05-15 10:24:36 |         2 | user3 | NULL  |
| heha      |            1 | 2018-05-15 10:36:11 |         2 | user3 | NULL  |
+-----------+--------------+---------------------+-----------+-------+-------+

我专门使用消息表中的 message_date 变量来对这些消息进行排序。

消息表:

+----+--------+----------+-----------+--------------+-----------------+---------------------+
| id | sender | receiver | message   | message_read | message_visible | message_date        |
+----+--------+----------+-----------+--------------+-----------------+---------------------+
|  1 |      4 |        2 | lulz      |            1 |               2 | 2018-05-15 10:24:36 |
|  2 |      1 |        4 | Hey Buddy |            1 |            NULL | 2018-05-10 11:58:39 |
|  3 |      2 |        4 | nooo      |            1 |               2 | 2018-05-15 10:24:36 |
|  4 |      4 |        2 | shut up   |            1 |               2 | 2018-05-15 10:24:36 |
|  5 |      4 |        2 | heha      |            1 |            NULL | 2018-05-15 10:36:11 |
|  6 |      1 |        4 | SUP MATE  |            1 |            NULL | 2018-05-15 11:04:24 |
+----+--------+----------+-----------+--------------+-----------------+---------------------+

我想得到结果

+-----------+--------------+---------------------+-----------+-------+-------+
| message   | message_read | message_date        | friend_id | name  | image |
+-----------+--------------+---------------------+-----------+-------+-------+
| SUP MATE  |            1 | 2018-05-15 11:04:24 |         1 | JUAN  | NULL  |
| heha      |            1 | 2018-05-15 10:36:11 |         2 | user3 | NULL  |
+-----------+--------------+---------------------+-----------+-------+-------+

请帮我解决这个问题。任何评论都会有所帮助!这也应该考虑到相同的 message_date。例如如果有两条消息的日期/时间完全相同，则只应从中选择一条。我正在考虑消息表的 id 来选择其中的一个。

Answer 1

有点像

select m.*
from 
message m
left join message m1 on (
  (
    (m.sender = m1.sender and m.receiver = m1.receiver)
                          or
    (m.sender = m1.receiver and m.receiver = m1.sender )
  )
   and case when m.message_date = m1.message_date
            then m.id < m1.id
            else m.message_date < m1.message_date
       end
)
where m1.id is null
and 4 in(m.sender, m.receiver)

我添加了 case 表达式来检查同一日期时间是否有超过 1 条消息，然后根据 id 选择最新的消息，我假设 id 设置为自动递增。

Demo