求个SQL语句,100分，速度2

Question

table1 (A1,B1)   
table2 (A2,B2,C2)
table1中数据：13632511111 10666666 N1  
13632511111 10666666 N2
13521451111 10654412 N1
13521451111 10654412 N2table2中数据13632511111 10666666 20100921 14:10:00
13632511111 10666666 20100921 11:11:01
13632511111 10666666 20100921 14:12:00
13632511111 10666666 20100921 14:13:0013521451111 10654412 20100921 14:10:00
13521451111 10654412 20100921 14:11:00
13521451111 10654412 20100921 14:12:00
13521451111 10654412 20100921 14:13:00也就是说table1与table2 中的数据可根据A1,B1来对应.但现在只需要将表2中时间较小的几条对应上去。
结果应该是这样：13632511111 10666666 N1 20100921 14:10:00
13632511111 10666666 N2 20100921 11:11:01
13521451111 10654412 N1 20100921 14:10:00
13521451111 10654412 N2 20100921 14:11:00请问这语句怎么写。
分不够可另开贴加分

Answer 1

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

Answer 2

select * from (select rownum rn,* from table1 t1,table2 t2
where t1.b1=t2.b2
order by t2.c2) where rn<10
具体列名哥不想写了，你自己把*改成列名就可以了

Answer 3

select A2,B2
from
(select A2,B2,C2,row_number() over(partition by A2,B2 order by C2 desc) rn
from table2) a,table1 b
where a.A2=b.A1 and a.B2=b.B1 and a.rn<3

Answer 4

貌似你们用的sqlserver的写法，很明显楼主要的是oracle的写法，即我这样的写法.

Answer 5

--是取较小的 看错了
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<3

Answer 6

这个分析函数这样的用法跟mssql的用法是一样的 你试试便知

Answer 7

--楼主，你要的答案！
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;

Answer 8

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>