在两个用户的通信之间选择最新消息并分组

Question

用户no = 1，是 Aa：

CREATE TABLE if not exists tblA
(
id int(11) NOT NULL auto_increment ,
sender varchar(255),
receiver varchar(255),
 msg varchar(255),
 date timestamp,
 PRIMARY KEY (id)
);
CREATE TABLE if not exists tblB
(
id int(11) NOT NULL auto_increment ,
sno varchar(255),
name varchar(255),
 PRIMARY KEY (id)
);

INSERT INTO tblA (sender, receiver,msg,date ) VALUES
('1', '2', 'buzz ...','2011-08-21 14:11:09'),
('1', '2', 'test ...','2011-08-21 14:12:19'),
('1', '2', 'check ...','2011-08-21 14:13:29'),
('1', '1', 'test2 ...','2011-08-21 14:14:09'),
('2', '1', 'check2 ...','2011-08-21 14:15:09'),
('2', '1', 'test3 ...','2011-08-21 14:16:09'),
('1', '2', 'buzz ...','2011-08-21 14:17:09'),
('1', '2', 'test ...','2011-08-21 14:18:19'),
('1', '2', 'check ...','2011-08-21 15:19:29'),
('1', '1', 'test2 ...','2011-08-21 14:10:09'),
('3', '1', 'check2 ...','2011-08-21 14:21:09'),
('3', '1', 'test3 ...','2011-08-21 14:22:09'),
('3', '2', 'buzz ...','2011-08-21 14:24:09'),
('3', '2', 'test ...','2011-08-21 14:25:19'),
('1', '3', 'check ...','2011-08-21 14:26:29'),
('1', '3', 'test2 ...','2011-08-21 14:27:09'),
('2', '3', 'check2 ...','2011-08-21 14:28:09'),
('2', '3', 'test3 ...','2011-08-21 14:29:09'),
('1', '2', 'check3 ...','2011-08-21 14:23:09'),
('1', '4', 'test2 ...','2011-08-21 14:27:09'),
('1', '5', 'test2 ...','2011-08-21 14:27:09'),
('2', '6', 'check2 ...','2011-08-21 14:28:09'),
('1', '7', 'test3 ...','2011-08-21 14:29:09'),
('8', '2', 'check3 ...','2011-08-21 14:23:09');

INSERT INTO tblB (sno, name ) VALUES
('1', 'Aa'),
('2', 'Bb'),
('3', 'Cc'),
('4', 'Dd'),
('5', 'Ee'),
('6', 'Ff'),
('7', 'Gg'),
('8', 'Hh');

如何获得最新的通信时间b/n 2用户。http://www.sqlfiddle.com/#！！

我真的很感谢您的任何帮助。

Answer 1

这是您想要的吗？

select b.name, max(date)
from tblA a join
     tblB b
     on b.sno in (a.receiver, a.sender)
where b.sno <> '1' and 
      exists (select 1
              from tblB b2
              where b2.sno = '1' and
                    b2.sno in (a.receiver, a.sender)
             ) or
      (a.sender = 1 and a.receiver = 1)
group by b.name
order by max(date) desc;

这返回了每个"彼此"与1交谈的最新交流时间。您的原始查询没有将max(date)放在选择列表中。

Answer 2

您最好的选择可能是使用UNION，例如：

select * from (
  select * from tblA
  where sender = '1' and receiver = '2'
  union
  select * from tblA
  where sender = '2' and receiver = '1'
) as t
order by date desc
limit 5

您可以做一些事情来使其更有效，例如将一些限制推向UNION的每个部分，但这是基本思想。

Answer 3

基于我以前答案的讨论，我认为您想要的是这样的：

SELECT
  tblA.id,
  sender.name AS sender_name,
  receiver.name AS receiver_name,
  tblA.msg,
  tblA.date
FROM (
  SELECT
    LEAST(sender, receiver) AS a,
    GREATEST(sender, receiver) AS b,
    MAX(id) AS id
  FROM tblA
  GROUP BY a, b
) AS t
JOIN tblA ON t.id = tblA.id
JOIN tblB AS sender ON tblA.sender = sender.sno
JOIN tblB AS receiver ON tblA.receiver = receiver.sno

所以基本思想是：

1. 选择最新消息id，auto_increment或对于消息（tblA）表，假定不断增加数字PRIMARY KEY的其他形式。这在不考虑sender或receiver订购的情况下找到这些消息的ID。
2. 基于每条消息的消息id，选择其完整详细信息。
3. 将消息行与用户（tblB）记录两次，一次获取sender详细信息，一次获取receiver详细信息。

Answer 4

尝试以下：

select p1.name,p2.name,max(date) from
(select least(sender,receiver) min_val,greatest(sender,receiver) max_val,msg,date from tblA
order by min_val,max_val,date) t inner join tblB p1 on t.min_val=p1.sno
inner join tblB p2 on t.max_val=p2.sno
group by min_val,max_val;