列不能外部连接到子查询

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


select * from A;

select * from B;



    --列不能外部连接到子查询
    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);
       --解决办法
       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);
    
    --
    select * from a;
    select * from b;
    
    select *
      from a
      left join (select b.* from b where b.actn_numb = 1) c
        on a.id = c.id;

 

转载于:https://www.cnblogs.com/zzzzw/p/10418183.html