SQL查询最新消息 [英] SQL query for most recent messages
问题描述
我正在尝试实现一个非常类似于facebook的消息系统.消息表是:
I am trying to implement a message system quite similar to facebook . The message table is :
+--------+----------+--------+-----+----------+
| msg_id | msg_from | msg_to | msg | msg_time |
+--------+----------+--------+-----+----------+
此处msg_from
和msg_to
包含用户ID,而msg_time
包含消息的时间戳.一个用户的用户ID可以同时出现在至"和来自"列中,而另一个用户可以多次出现.我应该如何编写一个SQL查询来选择两个用户之间最近发送的消息? (消息可以来自任何一个)1到2或2到1.
Here msg_from
and msg_to
contain user ids and the msg_time
contains the timestamp of the message . A user's user id can appear in both the to and from column and multiple times for another user . How should I write a SQL query which selects the most recent sent message between two users ? (The message can come from either one) 1 to 2 or 2 to 1 .
推荐答案
由于John Woo明确指出它不是定向的,所以这是我的新答案:
Since John Woo clarified that it is not directional, here's my new answer:
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演示
如果您最喜欢使用ANSI SQL,请按以下方法操作: http://sqlfiddle.com/#!2/0a575/19
SQLFiddle Demo
If ANSI SQL is your cup of tea, here's the way to do it: 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
) ;
这篇关于SQL查询最新消息的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!