A表(用户和 用户组 关系表)
UserID GrpID
U001 G01
U002 G01
U003 G02
U004 G02B表(用户 和用户组 接受消息表, 消息要么发送给用户,要么发送给用户组)
MsgID UserID GrpID MsgTime
1 U001 null 2009-12-04 10:10:10
1 null G001 2009-12-04 10:10:11
1 null G002 2009-12-04 10:10:12
2 null G001 2009-12-04 10:10:13
2 null G002 2009-12-04 10:10:14
如果我是用户 U001,我要得到的数据是
MsgID UserID GrpID MsgTime
1 U001 null 2009-12-04 10:10:10
2 null G001 2009-12-04 10:10:13 消息1,U001接受到,U001所在的工作组也接受到,同一消息只能以用户先接受到的为主
消息2,U001没有接受到,U001所在的工作组接受到,那么U001也可以查阅消息关键是消息数量非常的大,如何提高效率
我只能想到,先查出UserID=U001的消息,再到所有的消息中排除刚才的消息,
用用户对应的用户组再去查一次消息,肯定要用到 not Exists 或者 not In
我感觉效率很低,尤其数据量很大,可以上千万的时候很慢,特别请教好的算法谢谢,再次感谢!
UserID GrpID
U001 G01
U002 G01
U003 G02
U004 G02B表(用户 和用户组 接受消息表, 消息要么发送给用户,要么发送给用户组)
MsgID UserID GrpID MsgTime
1 U001 null 2009-12-04 10:10:10
1 null G001 2009-12-04 10:10:11
1 null G002 2009-12-04 10:10:12
2 null G001 2009-12-04 10:10:13
2 null G002 2009-12-04 10:10:14
如果我是用户 U001,我要得到的数据是
MsgID UserID GrpID MsgTime
1 U001 null 2009-12-04 10:10:10
2 null G001 2009-12-04 10:10:13 消息1,U001接受到,U001所在的工作组也接受到,同一消息只能以用户先接受到的为主
消息2,U001没有接受到,U001所在的工作组接受到,那么U001也可以查阅消息关键是消息数量非常的大,如何提高效率
我只能想到,先查出UserID=U001的消息,再到所有的消息中排除刚才的消息,
用用户对应的用户组再去查一次消息,肯定要用到 not Exists 或者 not In
我感觉效率很低,尤其数据量很大,可以上千万的时候很慢,特别请教好的算法谢谢,再次感谢!
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
b.*
from
a,b t
where
msgtime=(select min(msgtime) from b where msgid=t.msgid)
and
a.UserID=b.UserID
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 @userID = 'U001',@grpID = 'G001'
SELECT MsgID FROM [b表] WHERE UserID = @userID
UNION
SELECT MsgID FROM [b表] WHERE UserID IS NULL AND GrpID = @grpID
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
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
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.MsgIDdrop 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 行)
*/
关键是,我的那个不一定是时间,我只不过为了说明一下,其实我的那个很多是一些字符信息,看看我的例子 Create table UG (UID varchar(10), GID varchar(10))
insert into UG
select 'U001' as UId, 'G01' as GID
union
select 'U002' as UId, 'G01' as GID
union
select 'U003' as UId, 'G02' as GID
union
select 'U004' as UId, 'G02' as GIDselect * from UGCreate table MSG (MsgID int, UID varchar(10), GID varchar(10), Msg varchar(100))insert into MSg
select 1, 'U001', null, '1-U001-null'
union
select 1, null, 'G01', '1-null-G01'
union
select 2, null, 'G01', '2-null-G01'
union
select 2, null, 'G02', '2-null-G02'select * from UG
select * from MSGdeclare @U varchar(10)
set @U = 'U001'select * from MSg where UID = @U
union
select * from Msg as A where (A.GID = (select GID from UG where UID=@U)) and
(not exists(
select MsgID from Msg where UID = @U and MsgID =A.MsgId
))
我就是不想用 not Exists 我的消息数据会有千万左右,到那个时候我的服务器运行起来太慢了
如果不用 not in not Exists 就好了
(not exists(
select MsgID from Msg where UID = @U and MsgID =A.MsgId
))) or UID = @U试试看快点没
好像没有什么大的变化,有了 not Exists 效率搞不到哪里去呀 ,呵呵