消息表,查询一段时间内互发消息的所有用户,请帮忙优化
select distinct a.SenderID from (select a.ID, a.SenderID, a.ReceiverID
from messageTable a
where A.CreateTime >= '2007-02-01'
and A.CreateTime < '2007-02-08'
) a
, (select a.id, a.ReceiverID, a.SenderID
from messageTable a
where A.CreateTime >= '2007-02-01'
and A.CreateTime < '2007-02-08'
) b where a.SenderID =b.ReceiverID
and a.ReceiverID =
b.senderId
select distinct a.SenderID from (select a.ID, a.SenderID, a.ReceiverID
from messageTable a
where A.CreateTime >= '2007-02-01'
and A.CreateTime < '2007-02-08'
) a
, (select a.id, a.ReceiverID, a.SenderID
from messageTable a
where A.CreateTime >= '2007-02-01'
and A.CreateTime < '2007-02-08'
) b where a.SenderID =b.ReceiverID
and a.ReceiverID =
b.senderId
select distinct a.SenderID
from messageTable as a
where exists(select 1 from messageTable where ReceiverID=a.SenderID and SenderID=a.ReceiverID)
and A.CreateTime >= '2007-02-01' and A.CreateTime < '2007-02-08'
select
distinct a.SenderID
from
messageTable a
where
A.CreateTime between '2007-02-01' and '2007-02-08' and
exists
(
select 1 from from messageTable
where
SenderID=a.ReceiverID and ReceiverID=a.SenderID and id<>a.id
and CreateTime between '2007-02-01' and '2007-02-08'
)
select
distinct senderid
from
messageTable t
where exists
(select 1 from messageTable where SenderID=t.ReceiverID and ReceiverID=t.SenderID and t.CreateTime >= '2007-02-01' and t.CreateTime < '2007-02-08')