求一条SQL ，类似QQ的最近联系人

petshop4 2014-05-05 05:49:56
聊天表：
id, sendUserID, recvUserID, content
行1： 1, 1, 2, '2号你好'
行2： 2, 1, 3, '3号你好'
行3： 3, 1, 4, '4号你好'
行4： 4, 4, 1, '我是4号，1号你好烦'
行5： 5, 2, 1, '我是2号，1号你好烦'

想做成像QQ的最近聊天列表的方式，按ID倒序排序
希望结果如下：
talkAboutUserID，content，id
2， '我是2号，1号你好烦'，5
4， '我是4号，1号你好烦'，4
3， '3号你好'，2
...全文
285 15 打赏 收藏 转发到动态 举报
写回复
用AI写文章
15 条回复
切换为时间正序
请发表友善的回复…
发表回复
暗石绿 2014-05-09
  • 打赏
  • 举报
 回复
关注一下并帮顶。做一个坐享其成的。
习惯性蹭分 2014-05-07
  • 打赏
  • 举报
 回复
引用 5 楼 petshop4 的回复:
[quote=引用 3 楼 tangguangqiang 的回复:] 

if object_id('tempdb..#test') is not null
drop table #test
create  table #test(id int identity,sendUserID int,recvUserID int,content varchar(100))
insert into #test
select     1,   2,   '2号你好'    union
select      1,   3,   '3号你好'   union
select      1,   4,   '4号你好'   union
select      4,   1,   '我是4号，1号你好烦'  union
select     2,   1,   '我是2号，1号你好烦'

--select * from #test
select case when senduserid=1 then recvuserid else senduserid end talkAboutuserid,content,id from (
select id,'-'+cast(sendUserid as varchar)+'-'+cast(recvUserid as varchar)+'-' as liao,content,
sendUserid,recvUserid
from #test
)t where not exists (select * from #test where charindex('-'+cast(recvuserid as varchar)+'-',t.liao)>0
and charindex('-'+cast(senduserid as varchar)+'-',t.liao)>0
 and id>t.id) order by id desc
执行了一下， 排序不对，得到的排序是4,2,3 但是，2号才是最后说话的人；我希望的排序是2,，4，3[/quote] 是插入數據的時候，自增長的順序打亂了.按下面的試一下。 


if object_id('tempdb..#test') is not null
drop table #test
create  table #test(id int identity,sendUserID int,recvUserID int,content varchar(100))
insert into #test
select     1,   2,   '2号你好'    union all
select      1,   3,   '3号你好'   union all
select      1,   4,   '4号你好'   union all
select      4,   1,   '我是4号，1号你好烦'  union all
select     2,   1,   '我是2号，1号你好烦'

--select * from #test
select case when senduserid=1 then recvuserid else senduserid end talkAboutuserid,content,id from (
select id,'-'+cast(sendUserid as varchar)+'-'+cast(recvUserid as varchar)+'-' as liao,content,
sendUserid,recvUserid
from #test
)t where not exists (select * from #test where charindex('-'+cast(recvuserid as varchar)+'-',t.liao)>0
and charindex('-'+cast(senduserid as varchar)+'-',t.liao)>0
 and id>t.id) order by id desc
/*
2	我是2?，1?你好?	5
4	我是4?，1?你好?	4
3	3?你好	2
*/
连星入剑端 2014-05-06
  • 打赏
  • 举报
 回复
多加了个表Users，这张表应该是有的，看下是不是符合要求。 

IF OBJECT_ID('tempdb..#Users') IS NOT NULL
    BEGIN
        DROP TABLE #Users;
    END
GO

CREATE TABLE #Users
    (
      Id INT IDENTITY(1, 1)
             NOT NULL
             PRIMARY KEY ,
      Name VARCHAR(50) NOT NULL
    );
GO

INSERT  INTO #Users
VALUES  ( 'zhangsan' ),
        ( 'lisi' ),
        ( 'wangwu' ),
        ( 'zhaoliu' );
GO

IF OBJECT_ID('tempdb..#Messages') IS NOT NULL
    BEGIN
        DROP TABLE #Messages;
    END
GO

CREATE  TABLE #Messages
    (
      id INT IDENTITY ,
      sendUserID INT ,
      recvUserID INT ,
      content VARCHAR(100)
    );
    GO

INSERT  INTO #Messages
VALUES  ( 1, 2, '我是3号,2号你好' ),
        ( 1, 3, '3号你好' ),
        ( 1, 4, '4号你好' ),
        ( 4, 1, '我是4号，1号你好烦' ),
        ( 2, 1, '我是2号，1号你好烦' ),
        ( 3, 2, '我是3号，2号你好' );
GO

SELECT  *
FROM    #Users;
SELECT  *
FROM    #Messages;
GO

/*
查询标识为1的用户和其它用户的最后聊天记录；
*/
DECLARE @id INT = 1;

SELECT  t.*
FROM    #Users AS u
        CROSS APPLY ( SELECT TOP 1
                                *
                      FROM      #Messages AS m
                      WHERE     ( u.Id = m.sendUserId
                                  OR u.id = m.recvUserID
                                )
                                AND ( m.sendUserID = @id
                                      OR m.recvUserID = @id
                                    )
                      ORDER BY  id DESC
                    ) AS t
WHERE   u.id <> @id
ORDER BY t.id DESC;
GO
山寨DBA 2014-05-06
  • 打赏
  • 举报
 回复
你的意思就是1号给2、3、4发送了信息，如果2、4号回复了内容，3号没有回复，那结果就显示：1号给3号发送的消息 2号给1号回复的消息 4号给1号回复的消息 是这样吗？
petshop4 2014-05-06
  • 打赏
  • 举报
 回复
引用 9 楼 hwhmh2010 的回复:
[quote=引用 8 楼 petshop4 的回复:] 不对呀， 1号和3号的聊天没了 结果集需要的是 4 我是4号，1号你好烦 5 2 我是2号，1号你好烦 4 3 3号你好 3
仔细想想你的意思，是不是最后一次sendUserID 向recvUserID发送的消息？如果是这样的话，就把recvuserid加到那个Max的子查询里，多insert几条数据看看： 

create  table #test(id int identity,sendUserID int,recvUserID int,content varchar(100))
insert into #test
select     1,   2,   '2号你好'    union
select      1,   3,   '3号你好'   union
select      1,   4,   '4号你好'   union
select      4,   1,   '我是4号，1号你好烦'  union
select     2,   1,   '我是2号，1号你好烦' union
select     1,   2,   '2号你好222222'    union
select      1,   3,   '3号你好333333'   union
select      1,   4,   '4号你好44444'   union
select      4,   1,   '我是4号，1号你好烦444444'  union
select      1,   3,   '3号你好xxxxxxxxxx'   union
select      1,   4,   '4号你好xxxxxxx'   union
select      4,   1,   '我是4号，1号你好烦xxxxxxx'  union
select     2,   1,   '我是2号，1号你好烦222222'

select 
	a.sendUserID as talkAboutUserID,a.content,a.id 
from #test a inner join
(
	select 
		senduserid,recvuserid,max(id) as id 
	from #test group by sendUserID,recvuserid
) b on a.id=b.id
order by a.id desc
[/quote] 谢谢， 但是结果不是这样的， 你看QQ的最近聊天人列表， 不管是我对他人说话， 还是他人对我说话，都算对话，然后按对话的时间倒序； 也就是说，如果1号是我， 我和2,，3，4号对话过，那么； 需要的结果集里面就3条记录；
山寨DBA 2014-05-06
  • 打赏
  • 举报
 回复
引用 8 楼 petshop4 的回复:
不对呀， 1号和3号的聊天没了 结果集需要的是 4 我是4号，1号你好烦 5 2 我是2号，1号你好烦 4 3 3号你好 3
仔细想想你的意思，是不是最后一次sendUserID 向recvUserID发送的消息？如果是这样的话，就把recvuserid加到那个Max的子查询里，多insert几条数据看看： 

create  table #test(id int identity,sendUserID int,recvUserID int,content varchar(100))
insert into #test
select     1,   2,   '2号你好'    union
select      1,   3,   '3号你好'   union
select      1,   4,   '4号你好'   union
select      4,   1,   '我是4号，1号你好烦'  union
select     2,   1,   '我是2号，1号你好烦' union
select     1,   2,   '2号你好222222'    union
select      1,   3,   '3号你好333333'   union
select      1,   4,   '4号你好44444'   union
select      4,   1,   '我是4号，1号你好烦444444'  union
select      1,   3,   '3号你好xxxxxxxxxx'   union
select      1,   4,   '4号你好xxxxxxx'   union
select      4,   1,   '我是4号，1号你好烦xxxxxxx'  union
select     2,   1,   '我是2号，1号你好烦222222'

select 
	a.sendUserID as talkAboutUserID,a.content,a.id 
from #test a inner join
(
	select 
		senduserid,recvuserid,max(id) as id 
	from #test group by sendUserID,recvuserid
) b on a.id=b.id
order by a.id desc
petshop4 2014-05-06
  • 打赏
  • 举报
 回复
引用 7 楼 hwhmh2010 的回复:
[quote=引用 4 楼 petshop4 的回复:] [quote=引用 1 楼 hwhmh2010 的回复:] 

select sendUserID as talkAboutUserID,content,id from [聊天表] order by id desc
就order by id 不就行了嘛？
当然不行， 数据有5条，但是我查询的结果只想要3条； 就好比1号，和2,3,4号聊天， 我需要查出和2,，3，4最后的聊天记录[/quote] 我接用3楼的表结构，你看看下面的话是否可以出你想要的结果： 

create  table #test(id int identity,sendUserID int,recvUserID int,content varchar(100))
insert into #test
select     1,   2,   '2号你好'    union
select      1,   3,   '3号你好'   union
select      1,   4,   '4号你好'   union
select      4,   1,   '我是4号，1号你好烦'  union
select     2,   1,   '我是2号，1号你好烦'

select 
	a.sendUserID as talkAboutUserID,a.content,a.id 
from #test a inner join
(
	select 
		senduserid,max(id) as id 
	from #test group by sendUserID
) b on a.id=b.id 
order by a.id desc
结果： talkAboutUserID content id 4 我是4号，1号你好烦 5 2 我是2号，1号你好烦 4 1 4号你好 3[/quote] 不对呀， 1号和3号的聊天没了 结果集需要的是 4 我是4号，1号你好烦 5 2 我是2号，1号你好烦 4 3 3号你好 3
山寨DBA 2014-05-06
  • 打赏
  • 举报
 回复
引用 4 楼 petshop4 的回复:
[quote=引用 1 楼 hwhmh2010 的回复:] 

select sendUserID as talkAboutUserID,content,id from [聊天表] order by id desc
就order by id 不就行了嘛？
当然不行， 数据有5条，但是我查询的结果只想要3条； 就好比1号，和2,3,4号聊天， 我需要查出和2,，3，4最后的聊天记录[/quote] 我接用3楼的表结构，你看看下面的话是否可以出你想要的结果： 

create  table #test(id int identity,sendUserID int,recvUserID int,content varchar(100))
insert into #test
select     1,   2,   '2号你好'    union
select      1,   3,   '3号你好'   union
select      1,   4,   '4号你好'   union
select      4,   1,   '我是4号，1号你好烦'  union
select     2,   1,   '我是2号，1号你好烦'

select 
	a.sendUserID as talkAboutUserID,a.content,a.id 
from #test a inner join
(
	select 
		senduserid,max(id) as id 
	from #test group by sendUserID
) b on a.id=b.id 
order by a.id desc
结果： talkAboutUserID content id 4 我是4号，1号你好烦 5 2 我是2号，1号你好烦 4 1 4号你好 3
山寨DBA 2014-05-06
  • 打赏
  • 举报
 回复
引用 4 楼 petshop4 的回复:
[quote=引用 1 楼 hwhmh2010 的回复:] 

select sendUserID as talkAboutUserID,content,id from [聊天表] order by id desc
就order by id 不就行了嘛？
当然不行， 数据有5条，但是我查询的结果只想要3条； 就好比1号，和2,3,4号聊天， 我需要查出和2,，3，4最后的聊天记录[/quote] 懂了，先根据sendUserID，Max（id）找到最后一个聊天的id，然后再根据查出来的最后一个聊天的id关联下原表
petshop4 2014-05-06
  • 打赏
  • 举报
 回复
引用 3 楼 tangguangqiang 的回复:

if object_id('tempdb..#test') is not null
drop table #test
create  table #test(id int identity,sendUserID int,recvUserID int,content varchar(100))
insert into #test
select     1,   2,   '2号你好'    union
select      1,   3,   '3号你好'   union
select      1,   4,   '4号你好'   union
select      4,   1,   '我是4号，1号你好烦'  union
select     2,   1,   '我是2号，1号你好烦'

--select * from #test
select case when senduserid=1 then recvuserid else senduserid end talkAboutuserid,content,id from (
select id,'-'+cast(sendUserid as varchar)+'-'+cast(recvUserid as varchar)+'-' as liao,content,
sendUserid,recvUserid
from #test
)t where not exists (select * from #test where charindex('-'+cast(recvuserid as varchar)+'-',t.liao)>0
and charindex('-'+cast(senduserid as varchar)+'-',t.liao)>0
 and id>t.id) order by id desc
执行了一下， 排序不对，得到的排序是4,2,3 但是，2号才是最后说话的人；我希望的排序是2,，4，3
petshop4 2014-05-06
  • 打赏
  • 举报
 回复
引用 1 楼 hwhmh2010 的回复:

select sendUserID as talkAboutUserID,content,id from [聊天表] order by id desc
就order by id 不就行了嘛？
当然不行， 数据有5条，但是我查询的结果只想要3条； 就好比1号，和2,3,4号聊天， 我需要查出和2,，3，4最后的聊天记录
petshop4 2014-05-06
  • 打赏
  • 举报
 回复
引用 11 楼 hwhmh2010 的回复:
你的意思就是1号给2、3、4发送了信息，如果2、4号回复了内容，3号没有回复，那结果就显示：1号给3号发送的消息 2号给1号回复的消息 4号给1号回复的消息 是这样吗？
是这样的 而且还要按照时间排序，假设2号给1号回复了多条信息，只显示最后一条； 实际上微信的聊天列表就是这样做的
习惯性蹭分 2014-05-05
  • 打赏
  • 举报
 回复 

if object_id('tempdb..#test') is not null
drop table #test
create  table #test(id int identity,sendUserID int,recvUserID int,content varchar(100))
insert into #test
select     1,   2,   '2号你好'    union
select      1,   3,   '3号你好'   union
select      1,   4,   '4号你好'   union
select      4,   1,   '我是4号，1号你好烦'  union
select     2,   1,   '我是2号，1号你好烦'

--select * from #test
select case when senduserid=1 then recvuserid else senduserid end talkAboutuserid,content,id from (
select id,'-'+cast(sendUserid as varchar)+'-'+cast(recvUserid as varchar)+'-' as liao,content,
sendUserid,recvUserid
from #test
)t where not exists (select * from #test where charindex('-'+cast(recvuserid as varchar)+'-',t.liao)>0
and charindex('-'+cast(senduserid as varchar)+'-',t.liao)>0
 and id>t.id) order by id desc
无涯大者 2014-05-05
  • 打赏
  • 举报
 回复
是呀 Order by ID 就行了 不知道LZ是不是想要的结果
山寨DBA 2014-05-05
  • 打赏
  • 举报
 回复 

select sendUserID as talkAboutUserID,content,id from [聊天表] order by id desc
就order by id 不就行了嘛？
JAVA上百实例源码以及开源项目
　Java二进制IO类与文件复制操作实例，好像是一本书的例子，源代码有的是独立运行的，与同目录下的其它代码文件互不联系，这些代码面向初级、中级Java程序员。 Java访问权限控制源代码 1个目标文件 摘要:Java源码,...
java源码包---java 源码 大量 实例
　Java二进制IO类与文件复制操作实例，好像是一本书的例子，源代码有的是独立运行的，与同目录下的其它代码文件互不联系，这些代码面向初级、中级Java程序员。 Java访问权限控制源代码 1个目标文件 摘要:Java源码,...
2019小白SQL入门（零基础60分钟含SQL语法问答精彩）
零基础60分钟含SQL语法问答精彩
一个牛人提供的GIS源码(很好)
加上大陆地区太多的炮制太另人失望了(去年非常红火的百万首页，在大陆地区做的烂的一塌，只会炮制没有几个是有创意或者在人家创意的基础上增加自己创意的，唉)。 非常的希望大家能够给我意见或者建议，对大家提供的...
JAVA上百实例源码以及开源项目源代码
16个目标文件 内容索引:Java源码,初学实例,二进制,文件复制 Java二进制IO类与文件复制操作实例，好像是一本书的例子，源代码有的是独立运行的，与同目录下的其它代码文件互不联系，这些代码面向初级、中级Java程序员...
MS-SQL Server

34,594

社区成员

254,589

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章