选择两个用户通信之间的最新消息并将它们分组 [英] Select the Latest Message Between the communication of two users and group them

问题描述

User no=1 即 Aa :

User no=1 which is 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/#!2/ed676/1查询未提供最新的通信时间.

How to get latest communication time b/n 2 users . http://www.sqlfiddle.com/#!2/ed676/1 the query is not giving latest times of communication.

非常感谢您的帮助.提前致谢.

I really appreciate any help.Thanks in Advance.

推荐答案

这是您想要的吗?

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) 放入选择列表中.

This returns the latest time of communication for each "other person" talking to 1. Your original query didn't put max(date) in the select list.

这篇关于选择两个用户通信之间的最新消息并将它们分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！