select a.comm, max(decode(b3.no,1,b3.x,null)),max(decode(b3.no,2,b3.x,null)), max(decode(b3.no,3,b3.x,null)) from a,(select count(*) no, b1.comm,b1.x from b b1,b b2 where b1.comm=b2.comm and b1.rowid >= b2.rowid group by b1.comm,b1.x ) b3 where a.comm=b3.comm(+) group by a.comm
a
comm
-----
1
2
3b
comm x
-----------
1 1
1 2
1 3
2 1
2 3
结果:
comm x1 x2 x3
---------------------
1 1 2 3
2 1 null 3
3 null null null
http://expert.csdn.net/Expert/topic/1693/1693233.xml?temp=.3707392
from a,b where a.comm(+)=b.comm;
max(decode(b3.no,3,b3.x,null))
from a,(select count(*) no, b1.comm,b1.x from b b1,b b2
where b1.comm=b2.comm and b1.rowid >= b2.rowid
group by b1.comm,b1.x ) b3
where a.comm=b3.comm(+)
group by a.comm