首页新闻找找看学习计划

私信分组并显示最新一条记录,请问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
hillan的主页 hillan | 初学一级 | 园豆:157
提问于:2014-06-30 11:19
< >
分享
最佳答案
0

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

收获园豆:35
幻天芒 | 高人七级 |园豆:36522 | 2014-06-30 11:45

本来是整形的,只不过这里用字母代替是为了更加直观的显示出来。

hillan | 园豆:157 (初学一级) | 2014-06-30 11:47

@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
幻天芒 | 园豆:36522 (高人七级) | 2014-06-30 11:48

@幻天芒: fun_GetGroup这个是取分组的方法,基本就是将两个ID,弄成一个ID。

幻天芒 | 园豆:36522 (高人七级) | 2014-06-30 11:56

@幻天芒: fun_GetGroup 这个方法 返回的是什么?

hillan | 园豆:157 (初学一级) | 2014-06-30 11:56

@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
幻天芒 | 园豆:36522 (高人七级) | 2014-06-30 13:33

@幻天芒: 如果用是整形,更简单。直接按照大小排。

幻天芒 | 园豆:36522 (高人七级) | 2014-06-30 13:33
其他回答(3)
0

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

收获园豆:10
刘宏玺 | 园豆:14004 (专家六级) | 2014-06-30 11:27

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

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

支持(0) 反对(0) 刘宏玺 | 园豆:14004 (专家六级) | 2014-06-30 11:29

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

支持(0) 反对(0) hillan | 园豆:157 (初学一级) | 2014-06-30 11:31

@刘宏玺: 是的

支持(0) 反对(0) hillan | 园豆:157 (初学一级) | 2014-06-30 11:31

@hillan: 什么数据库呢?

支持(0) 反对(0) 刘宏玺 | 园豆:14004 (专家六级) | 2014-06-30 11:31

@刘宏玺: sqlserver

支持(0) 反对(0) hillan | 园豆:157 (初学一级) | 2014-06-30 11:32

@hillan: 我试试

支持(0) 反对(0) 刘宏玺 | 园豆:14004 (专家六级) | 2014-06-30 11:32

@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

支持(0) 反对(0) 刘宏玺 | 园豆:14004 (专家六级) | 2014-06-30 11:56

@刘宏玺: 

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按道理是不能相同的吧!

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

支持(1) 反对(0) 刘宏玺 | 园豆:14004 (专家六级) | 2014-06-30 12:51
0

这个你完全可以参考腾讯的QQ.

收获园豆:5
晓菜鸟 | 园豆:2594 (老鸟四级) | 2014-07-10 17:37
0

这个 用MySql如何写啊?

优美时光 | 园豆:202 (菜鸟二级) | 2015-04-06 22:06
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册