表A:A1,A2,
表B:B1,B2,
A和B的对应关系为一对多,
通过关联取A1,B2里的第一条显示
A:
1,3
2,4
B:
1,33
1,44
2,44
2,33
最后显示为
1,33
2,44
表B:B1,B2,
A和B的对应关系为一对多,
通过关联取A1,B2里的第一条显示
A:
1,3
2,4
B:
1,33
1,44
2,44
2,33
最后显示为
1,33
2,44
a inner join (
select b.*,row_number()over(partition by b1 order by rownum)rn
from b) b1
on a.a1=b1.b1
where b1.rn=1
select a.a1,b.b2, row_number() over(partition by a.a1) t from a,b where a.a1 = b.b1
)
where t = 1;
select a.a1,b.b2, row_number() over(partition by a.a1 order by rownum) t from a,b where a.a1 = b.b1
)
where t = 1;2L掉了order by
create table a(id number, runvalue number);
create table b(id number, runvalue number);insert into a values(1, 3);
insert into a values(2, 4);
insert into b values(1, 33);
insert into b values(1, 44);
insert into b values(2, 44);
insert into b values(2, 33);SQL> commit;
Commit complete
SQL> select * from a;
ID RUNVALUE
---------- ----------
1 3
2 4
SQL> select * from b;
ID RUNVALUE
---------- ----------
1 33
1 44
2 44
2 33
SQL> select a.id, b.runvalue
from a,
(select id, runvalue from b t1
where rowid in (select min(rowid) from b t2
where t1.id = t2.id)) b
where a.id = b.id;
ID RUNVALUE
---------- ----------
1 33
2 44
create or replace function F_TEST(str_in in varchar2 )
return varchar2 is
str varchar2(8000) default null;
begin
for x in (select t.b2 from b t where t.B1=str_in and rownum=1) loop
str:=x.b2;
end loop;
return str;
end F_TEST;--------------------------
select a1,b2 from (
select a.a1,b.b2, row_number() over(partition by a.a1 order by rownum) t from a,b where a.a1 = b.b1
)
where t = 1;
select a.a1,b.b2, row_number() over(partition by a.a1 order by rownum) t from a,b where a.a1 = b.b1
)
where t = 1;