求一效率高的SQL,在线等

山豆 2009-12-04 09:36:08
A表(用户和 用户组 关系表)
UserID GrpID
U001 G01
U002 G01
U003 G02
U004 G02

B表(用户 和用户组 接受消息表, 消息要么发送给用户,要么发送给用户组)
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
我感觉效率很低,尤其数据量很大,可以上千万的时候很慢,特别请教好的算法

谢谢,再次感谢!

...全文
153 17 打赏 收藏 转发到动态 举报
写回复
用AI写文章
17 条回复
切换为时间正序
请发表友善的回复…
发表回复
山豆 2009-12-13
  • 打赏
  • 举报
回复
我顶,有人帮帮我想想嘛?
再没有我就结贴了
山豆 2009-12-06
  • 打赏
  • 举报
回复
lg81807448
好像没有什么大的变化,有了 not Exists 效率搞不到哪里去呀 ,呵呵
lg81807448 2009-12-06
  • 打赏
  • 举报
回复
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
))) or UID = @U

试试看快点没
山豆 2009-12-06
  • 打赏
  • 举报
回复
可能用时间字段会产生误解,现在我把时间字段改成了 字符串
lg81807448 2009-12-06
  • 打赏
  • 举报
回复
把union换成or试试
山豆 2009-12-06
  • 打赏
  • 举报
回复
谢谢大家
关键是,我的那个不一定是时间,我只不过为了说明一下,其实我的那个很多是一些字符信息,看看我的例子



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 GID

select * from UG

Create 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 MSG

declare @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 就好了


山豆 2009-12-06
  • 打赏
  • 举报
回复
自己再顶
dawugui 2009-12-04
  • 打赏
  • 举报
回复
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 行)
*/
jiangshun 2009-12-04
  • 打赏
  • 举报
回复
--> 测试数据:[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
bancxc 2009-12-04
  • 打赏
  • 举报
回复
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
bancxc 2009-12-04
  • 打赏
  • 举报
回复
写了半天 没什么好办法
loworth 2009-12-04
  • 打赏
  • 举报
回复
刚发现你的msgTime同一消息不一样 那就不要msgtime了

加一索引(聚集的最好)到B表 字段UserID,GrpID

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
--小F-- 2009-12-04
  • 打赏
  • 举报
回复
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'
--小F-- 2009-12-04
  • 打赏
  • 举报
回复
select
b.*
from
a,b t
where
msgtime=(select min(msgtime) from b where msgid=t.msgid)
and
a.UserID=b.UserID
--小F-- 2009-12-04
  • 打赏
  • 举报
回复
select * from b t where msgtime=(select min(msgtime) from b where msgid=t.msgid)
loworth 2009-12-04
  • 打赏
  • 举报
回复

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
loworth 2009-12-04
  • 打赏
  • 举报
回复

UNION
试试

22,206

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

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