私信分组并显示最新一条记录，请问SQL语句如何写？
0
悬赏园豆：50
[已解决问题]
解决于 2014-08-04 09:28
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
最佳答案
0
senderId和receiverId是什么类型的？如果是整形比较简单，如果是字符型，需要稍微处理一下。
收获园豆：35
本来是整形的，只不过这里用字母代替是为了更加直观的显示出来。
@hillan:
SELECT * FROM ( SELECT *,rownum=ROW_NUMBER() OVER(PARTITION BY groupId ORDER BY postTime DESC) FROM ( SELECT *,groupId=dbo.fun_GetGroup(senderId,receiverId) FROM Msgs) AS t ) AS t1 WHERE t1.rownum=1
@幻天芒: fun_GetGroup这个是取分组的方法，基本就是将两个ID，弄成一个ID。
@幻天芒: fun_GetGroup 这个方法 返回的是什么？
@hillan:
CREATE FUNCTION fun_GetGroup ( @serderId VARCHAR(50), @receiverId VARCHAR(50) ) RETURNS VARCHAR(50) AS BEGIN DECLARE @TempTable TABLE(Val VARCHAR(50)) INSERT INTO @TempTable SELECT @serderId UNION SELECT @receiverId DECLARE @Result VARCHAR(50) SET @Result='' SELECT @Result=@Result+Val+',' FROM @TempTable ORDER BY Val RETURN isnull(@Result,'') END GO
@幻天芒: 如果用是整形，更简单。直接按照大小排。
其他回答(3)
0
A-B、A-C、A-D 分组是什么意思呢？没有理解
收获园豆：10
看懂了，你的意思是senderId和receiverId中只要有A-B、A-C、A-D就分组是吧
就是互相发过信息的分为一组
A-B、A-C、A-D 代表分组对象，比如A和B的聊天记录为一组，A和C的聊天记录为一组，A和D的聊天记录为一组，然后取出每一组的一条最新聊天记录。
@刘宏玺: 是的
@hillan: 什么数据库呢？
@刘宏玺: sqlserver
@hillan: 我试试
@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
@刘宏玺:
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按道理是不能相同的吧！
你不采纳倒是把原因说一下啊
0
这个你完全可以参考腾讯的QQ.
收获园豆：5
0
这个 用MySql如何写啊？
