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
12 回答

慕妹3242003
TA贡献1824条经验 获得超6个赞
A-B、A-C、A-D 代表分组对象,比如A和B的聊天记录为一组,A和C的聊天记录为一组,A和D的聊天记录为一组,然后取出每一组的一条最新聊天记录。

呼如林
TA贡献1798条经验 获得超3个赞
@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

心有法竹
TA贡献1866条经验 获得超5个赞
@刘宏玺:
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按道理是不能相同的吧!
你不采纳倒是把原因说一下啊
- 12 回答
- 0 关注
- 882 浏览
添加回答
举报
0/150
提交
取消