从a表中检索数据,条件是a表中的契约的用户ID必须是b表中最新更新者的id表a字段: 契约ID ContactId (PK), 用户ID userId ...
表b字段: 契约ID ContactId (PK), 序列号 no (PK), 更新者id updateUserId, 更新 日 updateDate...
表a和表b通过 契约ID 关联 select a....
from a,
(select b.* from b,
(select ContactId , max(updateDate) maxdate from b group by ContactId )c
where b.ContactId = c.ContactId and b.updateDate = c.maxdate
)temp
where a.ContactId = temp.ContactId and a.userId = temp.updateUserId上面这段sql文是可以达到目的,可是三层嵌套,效率肯定好慢,而且也觉得写的好白痴。有谁知道简化的写法,比上面更好的方法???比如什么分析函数之类的。 先谢了!!!!
表b字段: 契约ID ContactId (PK), 序列号 no (PK), 更新者id updateUserId, 更新 日 updateDate...
表a和表b通过 契约ID 关联 select a....
from a,
(select b.* from b,
(select ContactId , max(updateDate) maxdate from b group by ContactId )c
where b.ContactId = c.ContactId and b.updateDate = c.maxdate
)temp
where a.ContactId = temp.ContactId and a.userId = temp.updateUserId上面这段sql文是可以达到目的,可是三层嵌套,效率肯定好慢,而且也觉得写的好白痴。有谁知道简化的写法,比上面更好的方法???比如什么分析函数之类的。 先谢了!!!!
a表中的契约的用户ID必须是b表中最新更新者的id ==> 表a、b通过契约ID和用户ID与最新更新者的id 关联 select a.*
from a, b
where a.ContactID = b.ContactID and
a.UserID = b.UpdateUserID不知道理解的对不对。
(
select b.* from b,
(select ContactId,max(updateDate) updateDate from b group by ContactId) c
where b.ContactId=c.ContactId and b.updateDate=c.updateDate
) t
where a.ContactId - t.ContactId and a.userId = t.updateUserId
(
select b.* from b,
(select ContactId,max(updateDate) updateDate from b group by ContactId) c
where b.ContactId=c.ContactId and b.updateDate=c.updateDate
) t
where a.ContactId = t.ContactId and a.userId = t.updateUserId
(
select * from b c where updateDate = (select max(updatedate) from b where Contactid = c.Contactid)
) t
where a.ContactId = t.ContactId and a.userId = t.updateUserId
(
select Contract_ID,(rank() OVER (PARTITION BY ContactId ORDER BY updateDate desc) as RowNo,updateDate,updateUserId
From B )
where RowNo=1
我是在DB2中用过,Oracle 应该也可以吧....
from a,
(
select b.*,
row_number() over(partition by b.ContactId order by b.updateDate desc) as rn
from b
)tb
where rn = 1
and a.ContactId = tb.ContactId
and a.userId = tb.updateUserId;
Select a.* from a ,b t Where a.ContactId=t.ContactId and
a.userId=t.updateUserId and not exists(
Select * from b where ContactId=t.ContactId
and UpdateDate>t.updateDate)
另 not exists的是不是更慢,我这两个表数据狂多的
把数据多的表写在后面(t2表的数据比t1多)
from t1,t22.where条件,能过滤更多数据的条件写在后面
a.col1>b.col2能过滤的数据更多where a.id = b.secid
and a.col1>b.col2这样能一定程度的提高效率
因为sql 语句的解析是从右到左解析的.