17,377
社区成员
发帖
与我相关
我的任务
分享
SQL> select a1,b1,c2 from (select rownum rn,t1.a1,t1.b1,t2.c2 from table1 t1,table2 t2
2 where t1.b1=t2.b2(+)
3 order by t2.c2) where rn<10
4 ;
A1 B1 C2
-------------------------------------------------------------------------------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
13632511111 10666666 20100921 11:11:01
13632511111 10666666 20100921 11:11:01
13632511111 10666666 20100921 14:10:00
13521451111 10654412 20100921 14:10:00
13632511111 10666666 20100921 14:10:00
13632511111 10666666 20100921 14:12:00
13632511111 10666666 20100921 14:12:00
13632511111 10666666 20100921 14:13:00
13632511111 10666666 20100921 14:13:00
9 rows selected
SQL>
[Quote=引用 12 楼 js_flybird 的回复:]
select A2,B2
from(select A2,B2,C2,
row_number() over(partition by A2,B2 order by C2) rn
from table2) a,
table1 b
where a.A2=b.A1 and a.B2=b.B1 and a.rn<=2;
Connected to Oracle Database 10g Enterprise Edition Release 10.1.0.2.0
Connected as scott
SQL> select * from table1;
A1 B1
-------------------------------------------------------------------------------- --------------------------------------------------------------------------------
13632511111 10666666
13632511111 10666666
13521451111 10654412
13521451111 10654412
SQL> select * from table2;
A2 B2 C2
-------------------------------------------------------------------------------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
13632511111 10666666 20100921 14:10:00
13632511111 10666666 20100921 11:11:01
13632511111 10666666 20100921 14:12:00
13632511111 10666666 20100921 14:13:00
13521451111 10654412 20100921 14:10:00
13521451111 10654412 20100921 14:11:00
13521451111 10654412 20100921 14:12:00
13521451111 10654412 20100921 14:13:00
8 rows selected
SQL>
SQL> select a.a1,a.b1,b.c2 from
2 (select a1,b1,row_number()over(partition by a1,b1 order by a1,b1) rna from table1) a,
3 (select a2,b2,c2,row_number()over(partition by a2,b2 order by c2 ) rnb from table2 ) b
4 where a.a1=b.a2(+) and a.b1=b.b2(+) and a.rna=b.rnb(+);
A1 B1 C2
-------------------------------------------------------------------------------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
13521451111 10654412 20100921 14:10:00
13521451111 10654412 20100921 14:11:00
13632511111 10666666 20100921 11:11:01
13632511111 10666666 20100921 14:10:00
SQL>
select a.a1,a.b1,b.c2 from
(select a1,b1,row_number()over(partition by a1,b1 order by a1,b1) rna from table1) a
(select a2,b2,row_number()over(partition by a2,b2 order by c2 ) rnb from table2 ) b
where a.a1=b.a2(+) and a.b1=b.b2(+) and a.rna=b.rnb(+)
select a2,b2,c2 from (
select a2,b2,c2,row_number()over(partition by a2,b2 order by c2) rn
from table2 b
where exists(select 1 from table1 a where a.a1=b.a2 and a.b1=b.b2)
)
where rn<=2
select A.A1,A.B1,A.C1,B.C2
FROM
(
select A1,B1,C1,row_number(partition by (A1,B1) ORDER BY C1) as rn
from table2
) A,
--先选出TABLE2中,时间最小的2条
( select A2,B2,C2 from (
select A2,B2,C2,row_number(partition by (A2,B2) ORDER BY C2) as rn
from table2
) where rn<3
) B
WHERE A.A1=B.A2 AND A.B1=B.B2 AND A.RN=B.RN