私信分组并显示最新一条记录，请问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] aright join (select max([postTime]) time,casewhen [senderId] < [receiverId] then [senderId] + [receiverId]else [receiverId] + [senderId] end senderIdreceiverIdfrom [dbo].[Table_1]group by casewhen [senderId] < [receiverId] then [senderId] + [receiverId]else [receiverId] + [senderId] end) bon 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按道理是不能相同的吧！ 你不采纳倒是把原因说一下啊