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)