oracle连接中的子查询语句,Oracle在left join中使用子查询（ ORA-01799: 列不能外部联接到子查询）...

informix改oracle遇到一个问题。注意：left join的第二张表，有多条数据，只取符合条件的一条(本例中取出actn_numb最大的一条记录)。

基础数据准备：

create table A(

id number primary key,

act varchar2(100)

);

create table B(

id number,

actn_numb integer,

primary key(id,actn_numb)

);

insert into A(id,act) values(1,'art');

insert into A(id,act) values(2,'music');

insert into A(id,act) values(3,'sport');

insert into B(id,actn_numb) values(1,1);

insert into B(id,actn_numb) values(1,2);

insert into B(id,actn_numb) values(1,3);

insert into B(id,actn_numb) values(1,4);

insert into B(id,actn_numb) values(1,5);

insert into B(id,actn_numb) values(2,1);

insert into B(id,actn_numb) values(2,2);

insert into B(id,actn_numb) values(2,3);

insert into B(id,actn_numb) values(3,1);

informix语句：

select A.*,B.* from A

left join B

on A.id = B.id

and B.actn_numb=(select max(B.actn_numb) from B where B.id = a.id)

查询结果：

ID

ACT

ACT_NUMB

1

art

5

2

music

3

3

sport

1

在oracle中报错。( ORA-01799: 列不能外部联接到子查询)

原因分析：Oracle 不支持 在 join中存在子查询，效率太低。

oracle中正确写法：(借助分析函数row_number() over())

select A.*,C.ACTN_NUMB from A

left join (

select * from (

select B.*,(row_number() over(partition by id order by actn_numb desc)) rn

from B)

where rn=1 ) C

on (C.id=A.id)