求个SQL语句,240分，速度

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

--> 测试数据: #table1中数据:
if object_id('tempdb.dbo.#table1') is not null drop table #table1
go
create table #table1(A1 bigint,B1 int,c3 varchar(2))
insert into #table1
select 13632511111,10666666,'N1' union all
select 13632511111,10666666,'N2' union all
select 13521451111,10654412,'N1' union all
select 13521451111,10654412,'N2'
--> 测试数据: #table2
if object_id('tempdb.dbo.#table2') is not null drop table #table2
go
create table #table2 (A2 bigint,B2 int,C2 datetime )
insert into #table2
select 13632511111,10666666,'20100921 14:10:00'  union all
select 13632511111,10666666,'20100921 11:11:01'  union all
select 13632511111,10666666,'20100921 14:12:00' union all
select 13632511111,10666666,'20100921 14:13:00'  union all
select 13521451111,10654412,'20100921 14:10:00'  union all
select 13521451111,10654412,'20100921 14:11:00'  union all
select 13521451111,10654412,'20100921 14:12:00'  union all
select 13521451111,10654412,'20100921 14:13:00' SELECT A.*,B.C2 FROM  
(SELECT *,PX1=row_number()over(partition by A1,B1 ORDER BY C3 ) FROM #table1 )A,
(
select *,PX2=row_number()over(partition by A2,B2 ORDER BY C2 ) FROM #table2
)B
WHERE A1=A2 AND B1=B2 AND  PX1=PX2A1                   B1          c3   PX1                  C2
-------------------- ----------- ---- -------------------- -----------------------
13521451111          10654412    N1   1                    2010-09-21 14:10:00.000
13521451111          10654412    N2   2                    2010-09-21 14:11:00.000
13632511111          10666666    N1   1                    2010-09-21 11:11:01.000
13632511111          10666666    N2   2                    2010-09-21 14:10:00.000(4 行受影响)

Answer 2

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 3

楼主散分啊...
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 4

--也就是说table1与table2 中的数据可根据A1,B1来对应.但现在只需要将表2中时间较小的几条对应上去。
--就这样 以表记录少的为准
SQL> edi
已写入 file afiedt.buf  1   with tb1 as
  2   (select a1,b1,c3,row_number() over(partition by a1,b1 order by a1,b1) rn1 from table1),
  3   tb2 as
  4   (select a2,b2,c2,row_number() over(partition by a2,b2 order by c2) rn2 from table2)
  5   select a.a2,a.b2,to_char(a.c2,'yyyymmdd hh24:mi:ss') c2 from tb2 a,tb1 b
  6* where a.a2=b.a1 and a.b2=b.b1 and a.rn2=b.rn1
SQL> /          A2         B2 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

Answer 5

很遗憾的说row_number在SQL2000中无法识别。