试试:SELECT a.id , a.[sid] , a.sname , b.rid , b.rname , b.msg , b.[TIME] FROM TB a INNER JOIN TB b ON a.[SID] = b.rid AND a.sname = b.sname WHERE a.[sid] = @sid
select * from TB t where rid='001' and not exists (select 1 from TB where sid=t.sid and time>t.time)
试试SELECT a.id , a.[sid] , a.sname , b.rid , b.rname , b.msg , b.[TIME] FROM TB a full JOIN TB b ON a.[SID] = b.rid AND a.sname = b.sname WHERE a.[sid] = @sid
这个也不行 ,查不出消息内容 ,除了id,sid 其他全部显示 NULL
晕,原来我一直看错了提议--CREATE TABLE test(id INT , [sid] varchar(10) , sname varchar(10) , rid varchar(10) , rname varchar(10) , msg varchar(10) , [TIME] datetime) --INSERT INTO test --SELECT 1 , '001' , '李四' , '002' , '李四' , '哈哈' , '2012-12-19 16:31:13' --UNION ALL SELECT 2 , '001' , '李四' , '003' , '王五' , '测试' , '2012-12-19 16:39:31' --UNION ALL SELECT 3 , '002' , '李四' , '001' , '李四' , '嘻嘻' , '2012-12-19 16:39:46' --UNION ALL SELECT 4 , '003' , '王五' , '001' , '李四' , '测试' , '2012-12-19 16:40:11'SELECT * FROM test a WHERE EXISTS (SELECT 1 FROM (SELECT [sid],sname FROM test WHERE [sid]='001') b WHERE b.[sid]=a.[rid] AND a.[rname]=b.[sname])
/* drop TABLE test1 go CREATE TABLE test1( id int, sid VARCHAR(3), sname VARCHAR(3), rid VARCHAR(3), rname VARCHAR(3), msg VARCHAR(3), time# DATETIME ) go INSERT INTO test1 SELECT 1 , '001' , '李4' , '002' , '陈4' , 'h13' , '2012-12-19 16:31:13' UNION ALL SELECT 2 , '001' , '李4' , '003' , '王5' , 'taa' , '2012-12-19 16:59:31' UNION ALL SELECT 3 , '002' , '陈4' , '001' , '李4' , 'xxx' , '2012-12-19 16:49:46' UNION ALL SELECT 4 , '003' , '王5' , '001' , '李4' , 'cs2' , '2012-12-20 16:40:11' UNION ALL SELECT 5 , '005' , 'z4' , '001' , '王5' , 'taa' , '2012-12-19 16:59:34' UNION ALL SELECT 6 , '002' , '陈4' , '001' , '李4' , 'xx1' , '2012-12-19 16:49:43' UNION ALL SELECT 7 , '003' , '王5' , '001' , '李4' , 'c2' , '2012-12-20 16:40:12'
*/
DECLARE @sid VARCHAR(3) SELECT @sid='001';with cr2 as ( SELECT sid , sname , rid , rname, msg , time# from ( select row_number() over(partition by sname order by time# DESC) as rn ,* from test1 where rid=@sid)T2 WHERE rn=1 ), cr1 AS ( SELECT sid , sname , rid , rname, msg , time# from ( select row_number() over(partition by rname order by time# DESC) as rn ,* from test1 where sid=@sid)T1 WHERE rn=1 ) SELECT b.sname AS snmae,b.rname AS rname ,b.msg AS msg ,b.time# AS time# FROM cr1 a,cr2 b WHERE b.sid=a.rid AND b.time#>a.time# UNION ALL SELECT a.sname AS snmae,a.rname AS rname ,a.msg AS msg ,a.time# AS time# FROM cr1 a,cr2 b WHERE a.rid=b.sid AND a.time#>b.time#
declare @test TABLE(id INT , [sid] varchar(10) , sname varchar(10) , rid varchar(10) , rname varchar(10) , msg varchar(10) , TIME1 datetime) INSERT INTO @test SELECT 1 , '001' , '张三' , '002' , '李四' , '哈哈' , '2012-12-19 16:31:13' UNION ALL SELECT 2 , '002' , '李四' , '001' , '张三' , '测试' , '2012-12-19 16:39:31' UNION ALL SELECT 3 , '003' , '王五' , '001' , '张三' , '嘻嘻' , '2012-12-19 16:39:46' UNION ALL SELECT 4 , '004' , '赵六' , '001' , '张三' , '测试' , '2012-12-19 16:41:11' UNION ALL SELECT 5 , '001' , '张三' , '002' , '李四' , '测试' , '2012-12-20 16:42:11' UNION ALL SELECT 6 , '002' , '李四' , '001' , '张三' , '测试' , '2012-12-20 16:43:11'select * from @test where id in (select c.id from (select sname,rname,max(time1)time1 from (select id,sname,rname,time1 from @test where sid>rid union select id,rname,sname,time1 from @test where sid<rid)a group by sname,rname)b left join (select id,sname,rname,time1 from @test where sid>rid union select id,rname,sname,time1 from @test where sid<rid)c on c.sname=b.sname and c.rname=b.rname and c.time1=b.time1)
select a.* from TB a inner join (select max(id) Id,rid from TB group by rid) b on a.id=b.id where a.sid=@loginId查询所有登录人员loginId已发送的信息,同一个人的多条信息只发送最新的
DECLARE @sid VARCHAR(3) SELECT @sid='001' --SELECT sid , sname , rid , rname, msg , time# from ( select row_number() over(partition by sname order by time# DESC) as rn ,* from test1 where rid=@sid)T2 WHERE rn=1 -- SELECT sid , sname , rid , rname, msg , time# from ( select row_number() over(partition by sname order by time# DESC) as rn ,* from test1 where rid=@sid)T2 WHERE rn=1 --SELECT sid , sname , rid , rname, msg , time# from ( select row_number() over(partition by rname order by time# DESC) as rn ,* from test1 where sid=@sid)T1 WHERE rn=1 ;with cr2 as ( SELECT sid , sname , rid , rname, msg , time# from ( select row_number() over(partition by sname order by time# DESC) as rn ,* from test1 where rid=@sid)T2 WHERE rn=1 ), cr1 AS ( SELECT sid , sname , rid , rname, msg , time# from ( select row_number() over(partition by rname order by time# DESC) as rn ,* from test1 where sid=@sid)T1 WHERE rn=1 )SELECT b.sname AS snmae,b.rname AS rname ,b.msg AS msg ,b.time# AS time# FROM cr1 a,cr2 b WHERE ( b.sid=a.rid OR b.sid NOT IN (SELECT b.sid FROM cr1 a,cr2 b WHERE b.sid=a.rid)) AND b.time#>a.time# UNION SELECT a.sname AS snmae,a.rname AS rname ,a.msg AS msg ,a.time# AS time# FROM cr1 a,cr2 b WHERE ( a.rid=b.sid OR a.rid NOT IN (SELECT a.rid FROM cr1 a,cr2 b WHERE b.sid=a.rid)) AND a.time#>b.time# ORDER BY time# DESC
DECLARE @sid VARCHAR(3) SELECT @sid='001' ;with cr2 as ( SELECT sid , sname , rid , rname, msg , time# from ( select row_number() over(partition by sname order by time# DESC) as rn ,* from test1 where rid=@sid)T2 WHERE rn=1 ), cr1 AS ( SELECT sid , sname , rid , rname, msg , time# from ( select row_number() over(partition by rname order by time# DESC) as rn ,* from test1 where sid=@sid)T1 WHERE rn=1 )SELECT b.sname AS snmae,b.rname AS rname ,b.msg AS msg ,b.time# AS time# FROM cr1 a,cr2 b WHERE ( b.sid=a.rid OR b.sid NOT IN (SELECT b.sid FROM cr1 a,cr2 b WHERE b.sid=a.rid)) AND b.time#>a.time# UNION SELECT a.sname AS snmae,a.rname AS rname ,a.msg AS msg ,a.time# AS time# FROM cr1 a,cr2 b WHERE ( a.rid=b.sid OR a.rid NOT IN (SELECT a.rid FROM cr1 a,cr2 b WHERE b.sid=a.rid)) AND a.time#>b.time# ORDER BY time# DESC 弄漂亮一点。
-- data if object_id('tempdb.dbo.#') is not null drop table # create table #(id int, sid varchar(8), sname varchar(8), rid varchar(8), rname varchar(8), msg varchar(8), time datetime) insert into # select 1, '001', '张三', '002', '李四', '哈哈', '2012-12-20 10:35:33' union all select 2, '002', '李四', '001', '张三', '呵呵', '2012-12-20 10:35:38' union all select 3, '003', '王五', '001', '张三', '测试', '2012-12-20 10:34:33' union all select 4, '004', '赵六', '001', '张三', '你好', '2012-12-20 10:35:28' union all select 5, '001', '张三', '005', '葛七', '[表情]', '2012-12-20 10:36:26'-- result select * from # t where rid='001' and not exists (select 1 from # where rid='001' and sid=t.sid and time>t.time) -- 收到的最新信息 or sid='001' and not exists (select 1 from # where rid='001' and sid=t.rid) -- 发送的信息(对方尚未回应) /* id sid sname rid rname msg time 2 002 李四 001 张三 呵呵 2012-12-20 10:35:38 3 003 王五 001 张三 测试 2012-12-20 10:34:33 4 004 赵六 001 张三 你好 2012-12-20 10:35:28 5 001 张三 005 葛七 [表情] 2012-12-20 10:36:26 */-- new message insert # select 6, '005', '葛七', '001', '张三', '好啊', '2012-12-20 10:40:49'-- result select * from # t where rid='001' and not exists (select 1 from # where rid='001' and sid=t.sid and time>t.time) or sid='001' and not exists (select 1 from # where rid='001' and sid=t.rid) /* id sid sname rid rname msg time 2 002 李四 001 张三 呵呵 2012-12-20 10:35:38 3 003 王五 001 张三 测试 2012-12-20 10:34:33 4 004 赵六 001 张三 你好 2012-12-20 10:35:28 6 005 葛七 001 张三 好啊 2012-12-20 10:40:49 */
with temp as( select row_number() over (partition by a.talk_id order by b.time desc) as row_id, b.* from ( select rid as talk_id from msginfo where sid='001' union select sid as talk_id from msginfo where rid='001') a,msginfo b where (a.talk_id=b.sid or a.talk_id=b.rid) and (b.sid='001' or b.rid='001')) select * from temp where row_id=1;
1 001 张三 002 李四 哈哈 2012-12-19 16:31:13
2 001 张三 003 王五 测试 2012-12-19 16:39:31
3 002 李四 001 张三 嘻嘻 2012-12-19 16:39:46
4 003 王五 001 张三 测试 2012-12-19 16:40:11我想要的结果 id sid sname rid rname msg time
3 002 李四 001 张三 嘻嘻 2012-12-19 16:39:46
4 003 王五 001 张三 测试 2012-12-19 16:40:11
a.[sid] ,
a.sname ,
b.rid ,
b.rname ,
b.msg ,
b.[TIME]
FROM TB a
INNER JOIN TB b ON a.[SID] = b.rid
AND a.sname = b.sname
WHERE a.[sid] = @sid
a.[sid] ,
a.sname ,
b.rid ,
b.rname ,
b.msg ,
b.[TIME]
FROM TB a
full JOIN TB b ON a.[SID] = b.rid
AND a.sname = b.sname
WHERE a.[sid] = @sid
这个也不行 ,查不出消息内容 ,除了id,sid 其他全部显示 NULL
--INSERT INTO test
--SELECT 1 , '001' , '李四' , '002' , '李四' , '哈哈' , '2012-12-19 16:31:13'
--UNION ALL SELECT 2 , '001' , '李四' , '003' , '王五' , '测试' , '2012-12-19 16:39:31'
--UNION ALL SELECT 3 , '002' , '李四' , '001' , '李四' , '嘻嘻' , '2012-12-19 16:39:46'
--UNION ALL SELECT 4 , '003' , '王五' , '001' , '李四' , '测试' , '2012-12-19 16:40:11'SELECT *
FROM test a
WHERE EXISTS (SELECT 1 FROM (SELECT [sid],sname FROM test WHERE [sid]='001') b WHERE b.[sid]=a.[rid] AND a.[rname]=b.[sname])
要么就是两个元组来比较,只要前面一个元组的sname等于后面一个元组的rname,就取时间在后的那一条记录
id sid sname rid rname msg time
1 001 张三 002 李四 哈哈 2012-12-20 10:35:33
2 002 李四 001 张三 呵呵 2012-12-20 10:35:38
3 003 王五 001 张三 测试 2012-12-20 10:34:33
4 004 赵六 001 张三 你好 2012-12-20 10:35:28
5 001 张三 005 葛七 [表情] 2012-12-20 10:36:26
上面的数据表示
张三给李四发送了一条消息李四回复了张三
王五和赵六给张三各发送了一条消息张三没有回复
张三给葛七发送了一条消息葛七没有回复现在我以张三的身份 001 登陆想查询出如下数据id sid sname rid rname msg time
2 002 李四 001 张三 呵呵 2012-12-20 10:35:38
3 003 王五 001 张三 测试 2012-12-20 10:34:33
4 004 赵六 001 张三 你好 2012-12-20 10:35:28
5 001 张三 005 葛七 [表情] 2012-12-20 10:36:26这个时候如果 葛七 回复了张三的话完整的数据就如下
id sid sname rid rname msg time
1 001 张三 002 李四 哈哈 2012-12-20 10:35:33
2 002 李四 001 张三 呵呵 2012-12-20 10:35:38
3 003 王五 001 张三 测试 2012-12-20 10:34:33
4 004 赵六 001 张三 你好 2012-12-20 10:35:28
5 001 张三 005 葛七 [表情] 2012-12-20 10:36:26
6 005 葛七 001 张三 好啊 2012-12-20 10:40:49现在我再以张三的身份 001 登陆想查询出如下数据
id sid sname rid rname msg time
2 002 李四 001 张三 呵呵 2012-12-20 10:35:38
3 003 王五 001 张三 测试 2012-12-20 10:34:33
4 004 赵六 001 张三 你好 2012-12-20 10:35:28
6 005 葛七 001 张三 好啊 2012-12-20 10:40:49不知道这样各位能看懂吗 ?还望各位不吝赐教 !
/*
drop TABLE test1
go
CREATE TABLE test1(
id int,
sid VARCHAR(3),
sname VARCHAR(3),
rid VARCHAR(3),
rname VARCHAR(3),
msg VARCHAR(3),
time# DATETIME
)
go
INSERT INTO test1
SELECT 1 , '001' , '李4' , '002' , '陈4' , 'h13' , '2012-12-19 16:31:13'
UNION ALL SELECT 2 , '001' , '李4' , '003' , '王5' , 'taa' , '2012-12-19 16:59:31'
UNION ALL SELECT 3 , '002' , '陈4' , '001' , '李4' , 'xxx' , '2012-12-19 16:49:46'
UNION ALL SELECT 4 , '003' , '王5' , '001' , '李4' , 'cs2' , '2012-12-20 16:40:11'
UNION ALL SELECT 5 , '005' , 'z4' , '001' , '王5' , 'taa' , '2012-12-19 16:59:34'
UNION ALL SELECT 6 , '002' , '陈4' , '001' , '李4' , 'xx1' , '2012-12-19 16:49:43'
UNION ALL SELECT 7 , '003' , '王5' , '001' , '李4' , 'c2' , '2012-12-20 16:40:12'
*/
DECLARE @sid VARCHAR(3)
SELECT @sid='001';with
cr2 as
(
SELECT sid , sname , rid , rname, msg , time# from ( select row_number() over(partition by sname order by time# DESC) as rn ,* from test1 where rid=@sid)T2 WHERE rn=1
),
cr1 AS
(
SELECT sid , sname , rid , rname, msg , time# from ( select row_number() over(partition by rname order by time# DESC) as rn ,* from test1 where sid=@sid)T1 WHERE rn=1
) SELECT b.sname AS snmae,b.rname AS rname ,b.msg AS msg ,b.time# AS time# FROM cr1 a,cr2 b WHERE b.sid=a.rid AND b.time#>a.time#
UNION ALL
SELECT a.sname AS snmae,a.rname AS rname ,a.msg AS msg ,a.time# AS time# FROM cr1 a,cr2 b WHERE a.rid=b.sid AND a.time#>b.time#
declare @test TABLE(id INT , [sid] varchar(10) , sname varchar(10) , rid varchar(10) , rname varchar(10) , msg varchar(10) , TIME1 datetime)
INSERT INTO @test
SELECT 1 , '001' , '张三' , '002' , '李四' , '哈哈' , '2012-12-19 16:31:13'
UNION ALL SELECT 2 , '002' , '李四' , '001' , '张三' , '测试' , '2012-12-19 16:39:31'
UNION ALL SELECT 3 , '003' , '王五' , '001' , '张三' , '嘻嘻' , '2012-12-19 16:39:46'
UNION ALL SELECT 4 , '004' , '赵六' , '001' , '张三' , '测试' , '2012-12-19 16:41:11'
UNION ALL SELECT 5 , '001' , '张三' , '002' , '李四' , '测试' , '2012-12-20 16:42:11'
UNION ALL SELECT 6 , '002' , '李四' , '001' , '张三' , '测试' , '2012-12-20 16:43:11'select * from @test where id in (select c.id from (select sname,rname,max(time1)time1 from
(select id,sname,rname,time1 from @test where sid>rid union
select id,rname,sname,time1 from @test where sid<rid)a group by sname,rname)b left join
(select id,sname,rname,time1 from @test
where sid>rid union
select id,rname,sname,time1 from @test where sid<rid)c on c.sname=b.sname and c.rname=b.rname and c.time1=b.time1)
inner join (select max(id) Id,rid from TB group by rid) b on a.id=b.id
where a.sid=@loginId查询所有登录人员loginId已发送的信息,同一个人的多条信息只发送最新的
DECLARE @sid VARCHAR(3)
SELECT @sid='001'
--SELECT sid , sname , rid , rname, msg , time# from ( select row_number() over(partition by sname order by time# DESC) as rn ,* from test1 where rid=@sid)T2 WHERE rn=1
-- SELECT sid , sname , rid , rname, msg , time# from ( select row_number() over(partition by sname order by time# DESC) as rn ,* from test1 where rid=@sid)T2 WHERE rn=1
--SELECT sid , sname , rid , rname, msg , time# from ( select row_number() over(partition by rname order by time# DESC) as rn ,* from test1 where sid=@sid)T1 WHERE rn=1
;with
cr2 as
(
SELECT sid , sname , rid , rname, msg , time# from ( select row_number() over(partition by sname order by time# DESC) as rn ,* from test1 where rid=@sid)T2 WHERE rn=1
),
cr1 AS
(
SELECT sid , sname , rid , rname, msg , time# from ( select row_number() over(partition by rname order by time# DESC) as rn ,* from test1 where sid=@sid)T1 WHERE rn=1
)SELECT b.sname AS snmae,b.rname AS rname ,b.msg AS msg ,b.time# AS time# FROM cr1 a,cr2 b
WHERE ( b.sid=a.rid OR b.sid NOT IN (SELECT b.sid FROM cr1 a,cr2 b WHERE b.sid=a.rid)) AND b.time#>a.time#
UNION
SELECT a.sname AS snmae,a.rname AS rname ,a.msg AS msg ,a.time# AS time# FROM cr1 a,cr2 b
WHERE ( a.rid=b.sid OR a.rid NOT IN (SELECT a.rid FROM cr1 a,cr2 b WHERE b.sid=a.rid)) AND a.time#>b.time# ORDER BY time# DESC
DECLARE @sid VARCHAR(3)
SELECT @sid='001'
;with
cr2 as
(
SELECT sid , sname , rid , rname, msg , time# from ( select row_number() over(partition by sname order by time# DESC) as rn ,* from test1 where rid=@sid)T2 WHERE rn=1
),
cr1 AS
(
SELECT sid , sname , rid , rname, msg , time# from ( select row_number() over(partition by rname order by time# DESC) as rn ,* from test1 where sid=@sid)T1 WHERE rn=1
)SELECT b.sname AS snmae,b.rname AS rname ,b.msg AS msg ,b.time# AS time# FROM cr1 a,cr2 b
WHERE ( b.sid=a.rid OR b.sid NOT IN (SELECT b.sid FROM cr1 a,cr2 b WHERE b.sid=a.rid)) AND b.time#>a.time#
UNION
SELECT a.sname AS snmae,a.rname AS rname ,a.msg AS msg ,a.time# AS time# FROM cr1 a,cr2 b
WHERE ( a.rid=b.sid OR a.rid NOT IN (SELECT a.rid FROM cr1 a,cr2 b WHERE b.sid=a.rid)) AND a.time#>b.time# ORDER BY time# DESC 弄漂亮一点。
if object_id('tempdb.dbo.#') is not null drop table #
create table #(id int, sid varchar(8), sname varchar(8), rid varchar(8), rname varchar(8), msg varchar(8), time datetime)
insert into #
select 1, '001', '张三', '002', '李四', '哈哈', '2012-12-20 10:35:33' union all
select 2, '002', '李四', '001', '张三', '呵呵', '2012-12-20 10:35:38' union all
select 3, '003', '王五', '001', '张三', '测试', '2012-12-20 10:34:33' union all
select 4, '004', '赵六', '001', '张三', '你好', '2012-12-20 10:35:28' union all
select 5, '001', '张三', '005', '葛七', '[表情]', '2012-12-20 10:36:26'-- result
select * from # t
where rid='001' and not exists (select 1 from # where rid='001' and sid=t.sid and time>t.time) -- 收到的最新信息
or sid='001' and not exists (select 1 from # where rid='001' and sid=t.rid) -- 发送的信息(对方尚未回应)
/*
id sid sname rid rname msg time
2 002 李四 001 张三 呵呵 2012-12-20 10:35:38
3 003 王五 001 张三 测试 2012-12-20 10:34:33
4 004 赵六 001 张三 你好 2012-12-20 10:35:28
5 001 张三 005 葛七 [表情] 2012-12-20 10:36:26
*/-- new message
insert # select 6, '005', '葛七', '001', '张三', '好啊', '2012-12-20 10:40:49'-- result
select * from # t
where rid='001' and not exists (select 1 from # where rid='001' and sid=t.sid and time>t.time)
or sid='001' and not exists (select 1 from # where rid='001' and sid=t.rid)
/*
id sid sname rid rname msg time
2 002 李四 001 张三 呵呵 2012-12-20 10:35:38
3 003 王五 001 张三 测试 2012-12-20 10:34:33
4 004 赵六 001 张三 你好 2012-12-20 10:35:28
6 005 葛七 001 张三 好啊 2012-12-20 10:40:49
*/
select row_number() over (partition by a.talk_id order by b.time desc) as row_id,
b.*
from (
select rid as talk_id
from msginfo
where sid='001'
union
select sid as talk_id
from msginfo
where rid='001') a,msginfo b
where (a.talk_id=b.sid or a.talk_id=b.rid)
and (b.sid='001' or b.rid='001'))
select * from temp
where row_id=1;