select a.a1,a.a2,b.* from table1 a,table2 b where a.a1=b.b1(+) and a.a2=b.b2(+) and (b.b3 in (select max(b.b3) from table1 a,table2 b where a.a1=b.b1(+) and a.a2=b.b2(+)) or b.b3 is null);
select a.a1, a.a2, b.b1, b.b2, b.b3 from table1 a, ( select b1,b2,max(b3) b3 from table2 group by b1,b2 ) b where a.a1=b.b1(+) and a.a2=b.b2(+);
select b1,b2,max(b3) b3 from table2 group by b1,b2 这样得到的b3是什么呢?是整个列中的最大值吧?
a1 a2 a3
-----------
x1 y1 z1
x2 y2 z2
x3 y3 z3
x4 y4 z4
x5 y5 z5table2
b1 b2 b3 b4 b5
----------------
x1 y1 z1 // litter b3
x1 y1 z2 // bigger b3
x2 y2 z2
xx yy zzresult:
a1 a2 b1 b2 b3
-------------------
x1 y1 x1 y1 z2 // get bigger b3
x2 y2 x2 y2 z2
x3 y3 null null null
x4 y4 null null null
x5 y5 null null null
(
select b1,b2,max(b3) b3 from table2 group by b1,b2
) b
where a.a1=b.b1(+) and a.a2=b.b2(+);
这样得到的b3是什么呢?是整个列中的最大值吧?
得到的是按b1,b2分组的b3的最大值