mysql - 最近消息的 SQL 查询

Question

我正在尝试实现一个与 facebook 非常相似的消息系统。消息表是:

+--------+----------+--------+-----+----------+
| msg_id | msg_from | msg_to | msg | msg_time |
+--------+----------+--------+-----+----------+

这里的 msg_from 和 msg_to 包含用户 ID，msg_time 包含消息的时间戳。一个用户的用户 ID 可以同时出现在 to 和 from 列中，并且对于另一个用户可以多次出现。我应该如何编写一个 SQL 查询来选择两个用户之间最近发送的消息？ (消息可以来自任何一个) 1 to 2 或 2 to 1 。

Answer 1

既然吴宇森澄清了它不是定向的，这是我的新答案:

select *
from msgsList
where (least(msg_from, msg_to), greatest(msg_from, msg_to), msg_time)       
in 
(
    select 
       least(msg_from, msg_to) as x, greatest(msg_from, msg_to) as y, 
       max(msg_time) as msg_time
    from msgsList 
    group by x, y
);

输出:

| MSG_ID | MSG_FROM | MSG_TO |    MSG |                       MSG_TIME |
------------------------------------------------------------------------
|      1 |        1 |      2 |  hello | January, 23 2010 17:00:00-0800 |
|      5 |        1 |      3 | me too | January, 23 2012 00:15:00-0800 |
|      6 |        3 |      2 |  hello | January, 23 2012 01:12:12-0800 |

对于这个输入:

create table msgsList
(
  msg_id int,
  msg_from int, 
  msg_to int,
  msg varchar(10),
  msg_time datetime
);

insert into msgslist VALUES

(1, 1, 2, 'hello', '2010-01-23 17:00:00'),      -- shown
(2, 2, 1, 'world', '2010-01-23 16:00:00'),

(3, 3, 1, 'i am alive', '2011-01-23 00:00:00'),
(4, 3, 1, 'really', '2011-01-22 23:15:00'),
(5, 1, 3, 'me too', '2012-01-23 00:15:00'),     -- shown

(6, 3, 2, 'hello', '2012-01-23 01:12:12');      -- shown

SQLFiddle Demo

---

如果 ANSI SQL 是您的菜，那么这里是实现它的方法:http://sqlfiddle.com/#!2/0a575/19

select *
from msgsList z
where exists
(
    select null
    from msgsList
    where 
      least(z.msg_from, z.msg_to) = least(msg_from, msg_to)
      and greatest(z.msg_from, z.msg_to) = greatest(msg_from, msg_to)
    group by least(msg_from, msg_to), greatest(msg_from, msg_to)
    having max(msg_time) = z.msg_time  
) ;