22,206
社区成员
发帖
与我相关
我的任务
分享
Create table A(UserID nvarchar(10), GrpID nvarchar(10))
insert into A select
'U001', 'G001' union all select
'U002', 'G001' union all select
'U003', 'G002' union all select
'U004', 'G002'
go
Create table B(MsgID nvarchar(10), UserID nvarchar(10), GrpID nvarchar(10), MsgTime Datetime)
insert into B select
'1' ,'U001', null ,'2009-12-04 10:10:10' union all select
'1' ,null ,'G001' ,'2009-12-04 10:10:11' union all select
'1' ,null ,'G002' ,'2009-12-04 10:10:12' union all select
'2' ,null ,'G001' ,'2009-12-04 10:10:13' union all select
'2' ,null ,'G002' ,'2009-12-04 10:10:14'
go
select t.MsgID , a.UserID , a.GrpID , t.MsgTime from a,b t where (a.UserID = t.UserID or a.GrpID = t.GrpID) and a.UserID = 'U001'
and t.MsgTime = (select min(MsgTime) from b where MsgID = t.MsgID)
order by t.MsgID
drop table a , b
/*
MsgID UserID GrpID MsgTime
---------- ---------- ---------- ------------------------------------------------------
1 U001 G001 2009-12-04 10:10:10.000
2 U001 G001 2009-12-04 10:10:13.000
(所影响的行数为 2 行)
*/
--> 测试数据:[TA]
if object_id('[TA]') is not null drop table [TA]
create table [TA]([UserID] varchar(4),[GrpID] varchar(4))
insert [TA]
select 'U001','G001' union all
select 'U002','G001' union all
select 'U003','G002' union all
select 'U004','G002'
--> 测试数据:[TB]
if object_id('[TB]') is not null drop table [TB]
create table [TB]([MsgID] int,[UserID] varchar(4),[GrpID] varchar(4),[MsgTime] datetime)
insert [TB]
select 1,'U001',null,'2009-12-04 10:10:10' union all
select 1,null,'G001','2009-12-04 10:10:11' union all
select 1,null,'G002','2009-12-04 10:10:12' union all
select 2,null,'G001','2009-12-04 10:10:13' union all
select 2,null,'G002','2009-12-04 10:10:14'
select MsgID,B.UserID,B.GrpID,MsgTime from [TA],TB B where (TA.UserID=B.UserID or TA.GrpID=B.GrpID) and TA.UserID='U001'
and not exists(select 1 from TB where B.MsgID=MsgID and MsgTime<B.MsgTime)
/*
MsgID UserID GrpID MsgTime
----------- ------ ----- ------------------------------------------------------
1 U001 NULL 2009-12-04 10:10:10.000
2 NULL G001 2009-12-04 10:10:13.000
(所影响的行数为 2 行)
*/
drop table TA,TB
Create table A(UserID nvarchar(10), GrpID nvarchar(10))
insert into A select
'U001', 'G001' union all select
'U002', 'G001' union all select
'U003', 'G002' union all select
'U004', 'G002'
go
Create table B(MsgID nvarchar(10), UserID nvarchar(10), GrpID nvarchar(10), MsgTime Datetime)
insert into B select
'1' ,'U001', null ,'2009-12-04 10:10:10' union all select
'1' ,null ,'G001' ,'2009-12-04 10:10:11' union all select
'1' ,null ,'G002' ,'2009-12-04 10:10:12' union all select
'2' ,null ,'G001' ,'2009-12-04 10:10:13' union all select
'2' ,null ,'G002' ,'2009-12-04 10:10:14'
go
select B.* from B
inner join A A1 On (A1.GrpID=B.GrpID or A1.UserID=B.UserID)
where A1.UserID='U001'
/*MsgID UserID GrpID MsgTime
---------- ---------- ---------- ------------------------------------------------------
1 U001 NULL 2009-12-04 10:10:10.000
1 NULL G001 2009-12-04 10:10:11.000
2 NULL G001 2009-12-04 10:10:13.000
(所影响的行数为 3 行)*/
drop table A,B
DECLARE @userID AS VARCHAR(10),@grpID AS VARCHAR(10)
SELECT @userID = 'U001',@grpID = 'G001'
SELECT MsgID FROM [b表] WHERE UserID = @userID
UNION
SELECT MsgID FROM [b表] WHERE UserID IS NULL AND GrpID = @grpID
select
b.*
from
a,b t
where
msgtime=(select min(msgtime) from b where msgid=t.msgid)
and
a.UserID=b.UserID
and
a.UserID='U001'
select
b.*
from
a,b t
where
msgtime=(select min(msgtime) from b where msgid=t.msgid)
and
a.UserID=b.UserID
select * from b t where msgtime=(select min(msgtime) from b where msgid=t.msgid)
DECLARE @userID AS VARCHAR(10),@grpID AS VARCHAR(10)
SELECT @userID = 'U001',@grpID = 'G001'
SELECT MsgID,MsgTime FROM [b表] WHERE UserID = @userID
UNION
SELECT MsgID,MsgTime FROM [b表] WHERE GrpID = @grpID