私信分组并显示最新一条记录，请问SQL语句如何写？

Question

create table tbl_message ( id int primary key identity(1,1), --主键 senderId int not null, --发件人 receiverId int not null, --收件人 content nvarchar(500) not null, --私信内容 postTime datetime default(getdate()) not null --发送时间 ) insert into tbl_message(senderId,receiverId,content) values(1,2,'hi,how are you?') insert into tbl_message(senderId,receiverId,content) values(1,3,'hi,how are you?') insert into tbl_message(senderId,receiverId,content) values(1,4,'hi,how are you?') insert into tbl_message(senderId,receiverId,content) values(2,1,'fine,thank you! and you?') insert into tbl_message(senderId,receiverId,content) values(3,1,'not bad!') insert into tbl_message(senderId,receiverId,content) values(4,1,'a bad day!') insert into tbl_message(senderId,receiverId,content) values(1,2,'i am fine too!')   下面举例子： id senderId receiverId content postTime ----------- ----------- ----------- ------------------------------------- 1 A B hi,how are you? 2014-06-30 2 A C hi,how are you? 2014-06-30 3 A D hi,how are you? 2014-06-30 4 B A fine,thank you! and you? 　　2014-06-31 5 C A not bad! 　　2014-06-31 6 D A a bad day! 2014-06-31 7 A B i am fine too! 2014-06-32 如上表中：A-B、A-C、A-D 分组，然后每组取最新一条记录，结果如下三组 id senderId receiverId content postTime ----------- ----------- ----------- ------------------------------------- 5 C A not bad! 　　2014-06-31 6 D A a bad day! 2014-06-31 7 A B i am fine too! 2014-06-32

Answer 1

senderId和receiverId是什么类型的？如果是整形比较简单，如果是字符型，需要稍微处理一下。

Answer 2

A-B、A-C、A-D 分组是什么意思呢？没有理解

Answer 3

看懂了，你的意思是senderId和receiverId中只要有A-B、A-C、A-D就分组是吧

就是互相发过信息的分为一组

Answer 4

A-B、A-C、A-D 代表分组对象，比如A和B的聊天记录为一组，A和C的聊天记录为一组，A和D的聊天记录为一组，然后取出每一组的一条最新聊天记录。

Answer 5

@刘宏玺: 是的

Answer 6

@hillan: 什么数据库呢？

Answer 7

@刘宏玺: sqlserver

Answer 8

@hillan: 我试试

Answer 9

@hillan: 

select [id],[senderId],[receiverId],[content],[postTime] from [dbo].[Table_1] a
right join
(
select max([postTime]) time,
case
when [senderId] < [receiverId] then [senderId] + [receiverId]
else [receiverId] + [senderId]
end senderIdreceiverId
from [dbo].[Table_1]
group by
case
when [senderId] < [receiverId] then [senderId] + [receiverId]
else [receiverId] + [senderId]
end
) b
on a.[postTime] = b.time

Answer 10

@刘宏玺: 

select [id],[senderId],[receiverId],[content],[postTime] from [dbo].[tbl_message] a
right join 
(
select  max([postTime]) time,
case
when [senderId] < [receiverId]  then CAST([senderId] AS nvarchar(20)) + '|' + CAST([receiverId] AS nvarchar(20))
else CAST([receiverId] AS nvarchar(20)) + '|'+ CAST([senderId] AS nvarchar(20))
end senderIdreceiverId
from [dbo].[tbl_message]
group by 
case
when [senderId] < [receiverId]  then CAST([senderId] AS nvarchar(20)) + '|'+ CAST([receiverId] AS nvarchar(20))
else CAST([receiverId] AS nvarchar(20)) + '|'+ CAST([senderId] AS nvarchar(20))
end
) b
on a.[postTime] = b.time

老兄，我这个都实现了，但是的的postTime是不能相同的，我觉得这个postTime按道理是不能相同的吧！

你不采纳倒是把原因说一下啊

Answer 11

这个 用MySql如何写啊？