TABLE-S6
CODE
T1
T2
T3
TABLE-S9
CODE CARCODE
T1 AA
T1 BB
T2 CC
TABLE-R2
CODE CARCODE DATE TAG
T1 AA 2007-11-10 10
T1 AA 2007-11-12 11
T1 AA 2007-11-11 20
需要结果
CODE CARCCODE DATE TAG
T1 AA 2007-11-12 11
T1 BB
T2 CC
T3
CODE
T1
T2
T3
TABLE-S9
CODE CARCODE
T1 AA
T1 BB
T2 CC
TABLE-R2
CODE CARCODE DATE TAG
T1 AA 2007-11-10 10
T1 AA 2007-11-12 11
T1 AA 2007-11-11 20
需要结果
CODE CARCCODE DATE TAG
T1 AA 2007-11-12 11
T1 BB
T2 CC
T3
select s6.code,s9.carcode,aa.date,aa.tag from s6
left join s9
on s6.code=s9.code
left join
(select * from r2 a,
(select code,carcode, max(date) from r2 group by code,carcode) b
where a.code=b.code and a.carcode=b.carcode and a.date=b.date) aa //取得最大date记录后的表
on s9.code=aa.code and s9.carcode=aa.carcode
select * from
(select s6.code,s9.carcode from ms06 s6 ,ms09 s9 where s6.code=s9.code(+) )M ,
(select code,carcode,max(ddate) from tr02 r2 group by code,carcode) T
where m.code=t.code(+) and m.carcode=t.carcode(+)不知道我这个方法效率怎么样?或者有效率高的,请高人指点
From s6 a Left Join s9 b On a.code=b.code
Left Join (Select * From r3 a Where a.date1 In(Select Max(date) From r3 Group By carcode)) c
On b.carcode=c.carcode
Order By a.code