求个SQL语句,100分，速度

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 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

Answer 2

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 3

我说怎么table1怎么还多了一列数据.
试试上面的sql吧.

Answer 4

不一定是3条，具体条数要与table1中的条数一致

Answer 5

select * from (select rownum rn,* from table1 t1,table2 t2
where t1.b1=t2.b2
order by t2.c2) where rn<10

Answer 6

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(+)

Answer 7

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

Answer 8

最终完美版如下：
select a1,b1,c2 from (select rownum rn,t1.a1,t1.b1,t2.c2 from table1 t1,table2 t2
where t1.b1=t2.b2(+)
order by t2.c2) where rn<10

Answer 9

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> 

Answer 10

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 11

帮你测试下.
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>