Eason [資料科學//Python學習/資料庫] & [拍片&剪片]

案例來源:ITPUB

表1：create table tst001 (a varchar2(100),b varchar2(100));
insert into tst001 values(1,'test001');
insert into tst001 values(1,'test002');
insert into tst001 values(1,'test003');
insert into tst001 values(1,'test004');

表2：
create table tst002 (a varchar2(100),b varchar2(100),c varchar2(100));
insert into tst002 values(1,'abc',1);
insert into tst002 values(1,'abcd',2);
insert into tst002 values(1,'abcde',3);
insert into tst002 values(1,'abcdef',4);


結果:

f_id f_name    id        name

1    test001   1      test001
1    test001   2      test002
1    test001   3      test003
1    test001   4      test004

解法一: 多層子查詢(運用到兩張表)

*********************************

select * from tst001;
select * from tst002;

select t2.a,t1.b,t2.c,b.b from tst001 t1,tst002 t2,(select * from (
select rownum num ,b from tst001)) b where t1.b='test001' and t2.c=b.num ;

解法二:substr用法

************************

select a.a,a.b,b.c,substr(a.b,1,6)||b.c
from tst001 a,tst002 b
where a.b='test001';

解法三:LEVEL方法 (投機取巧)

select 1,'test001',level,'test00'||level from dual connect by level<=4 order by 3;