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
当然不行, 数据有5条,但是我查询的结果只想要3条; 就好比1号,和2,3,4号聊天, 我需要查出和2,,3,4最后的聊天记录 我接用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
当然不行, 数据有5条,但是我查询的结果只想要3条; 就好比1号,和2,3,4号聊天, 我需要查出和2,,3,4最后的聊天记录 我接用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 不对呀, 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
仔细想想你的意思,是不是最后一次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
多加了个表Users,这张表应该是有的,看下是不是符合要求。IF OBJECT_ID('tempdb..#Users') IS NOT NULL BEGIN DROP TABLE #Users; END GOCREATE TABLE #Users ( Id INT IDENTITY(1, 1) NOT NULL PRIMARY KEY , Name VARCHAR(50) NOT NULL ); GOINSERT INTO #Users VALUES ( 'zhangsan' ), ( 'lisi' ), ( 'wangwu' ), ( 'zhaoliu' ); GOIF OBJECT_ID('tempdb..#Messages') IS NOT NULL BEGIN DROP TABLE #Messages; END GOCREATE TABLE #Messages ( id INT IDENTITY , sendUserID INT , recvUserID INT , content VARCHAR(100) ); GOINSERT 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号你好' ); GOSELECT * 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
执行了一下, 排序不对,得到的排序是4,2,3 但是,2号才是最后说话的人;我希望的排序是2,,4,3是插入數據的時候,自增長的順序打亂了.按下面的試一下。 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 */
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
当然不行, 数据有5条,但是我查询的结果只想要3条;
就好比1号,和2,3,4号聊天, 我需要查出和2,,3,4最后的聊天记录
懂了,先根据sendUserID,Max(id)找到最后一个聊天的id,然后再根据查出来的最后一个聊天的id关联下原表
当然不行, 数据有5条,但是我查询的结果只想要3条;
就好比1号,和2,3,4号聊天, 我需要查出和2,,3,4最后的聊天记录
我接用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
当然不行, 数据有5条,但是我查询的结果只想要3条;
就好比1号,和2,3,4号聊天, 我需要查出和2,,3,4最后的聊天记录
我接用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
不对呀, 1号和3号的聊天没了
结果集需要的是
4 我是4号,1号你好烦 5
2 我是2号,1号你好烦 4
3 3号你好 3
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
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
谢谢, 但是结果不是这样的, 你看QQ的最近聊天人列表, 不管是我对他人说话, 还是他人对我说话,都算对话,然后按对话的时间倒序;
也就是说,如果1号是我, 我和2,,3,4号对话过,那么;
需要的结果集里面就3条记录;
BEGIN
DROP TABLE #Users;
END
GOCREATE TABLE #Users
(
Id INT IDENTITY(1, 1)
NOT NULL
PRIMARY KEY ,
Name VARCHAR(50) NOT NULL
);
GOINSERT INTO #Users
VALUES ( 'zhangsan' ),
( 'lisi' ),
( 'wangwu' ),
( 'zhaoliu' );
GOIF OBJECT_ID('tempdb..#Messages') IS NOT NULL
BEGIN
DROP TABLE #Messages;
END
GOCREATE TABLE #Messages
(
id INT IDENTITY ,
sendUserID INT ,
recvUserID INT ,
content VARCHAR(100)
);
GOINSERT 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号你好' );
GOSELECT *
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
但是,2号才是最后说话的人;我希望的排序是2,,4,3是插入數據的時候,自增長的順序打亂了.按下面的試一下。
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
*/