SQL--LEFT JOIN 右表多条数据解决方案

用到SQL中的LEFT JOIN时，在右表中存在多条记录，即一对多的情况时，
表结构与数据：

--Oracle左连接返回多条记录中一条记录的查询语句
--创建表1并插入测试数据
create table table1(ANAME varchar(20));
insert into table1 values('alan');
insert into table1 values('Alee');
insert into table1 values('aspn');

--创建表2并插入测试数据
create table table2
(
BNo varchar(10),
BTell varchar(20),
ANAME varchar(20)
);
insert into table2 values('NO1','1366666','alan');
insert into table2 values('NO1','1388888','alan');
insert into table2 values('NO1','1399999','alan');
insert into table2 values('NO2','1333333','Alee');
insert into table2 values('NO3','1311111','aspn');
insert into table2 values('NO3','1322222','aspn');

--执行关联查询
select a.*,t.*
from table1 a left join (select * from(select b.*,row_number() over (partition by bno order by BTell desc) as px
                                from table2 b) e where px = 1) t
    on a.ANAME = t.ANAME;
/*    
执行结果：
  ANAME BNO BTELL   ANAME PX                
1  alan  NO1  1399999  alan  1
2  Alee  NO2  1333333  Alee  1
3  aspn  NO3  1322222  aspn  1
*/