两个表
表A: 主健1 主健2 开始港code 终了港code
1 1 001 002
1 2 002 003
1 3 003 004表B: 港code 国code
001 C(和谐)N
002 JP
003 US
004 AU现在知道表A的主健1 = '1',我想要按照主健2的升顺取出到达的所有港的国code(不能重复),应该怎么取呢?
(如上数据,取出来的结果应该是4条数据,分别是CN,JP,US,AU。因为表A中到达港的顺序是001-->002-->003-->004)
表A: 主健1 主健2 开始港code 终了港code
1 1 001 002
1 2 002 003
1 3 003 004表B: 港code 国code
001 C(和谐)N
002 JP
003 US
004 AU现在知道表A的主健1 = '1',我想要按照主健2的升顺取出到达的所有港的国code(不能重复),应该怎么取呢?
(如上数据,取出来的结果应该是4条数据,分别是CN,JP,US,AU。因为表A中到达港的顺序是001-->002-->003-->004)
解决方案 »
- oracle 的隐含参数_cursor_features_enabled
- 不同表结构的列复制
- 关于-ORA-00600: internal error code, arguments: [kokbcvb1], []错误,期待请高人指点!
- oracle SQL Loader 读取csv 开头行bom问题(乱码)
- 缓存的问题
- 超高难度的sql查询语句,能行你就来(老难了,要有心里准备)
- pl/sql develop 存储过程 pls-00103
- 初学oracle 有几点问题
- 哪位仁兄知道上海电信用的是什么数据库?
- CentOS 7上装Oracle 12c出现问题,ora-00600怎么解决?
- 如何将一台机子上的oracle数据库完整地转移到另一台机子上
- -- 闲的没事,出一道小考题:如何用外部表查看报警日志中昨天以来产生的错误?--
start with ... connect by ...
--没测试过,试试:
select b.国code from b ,a
where a.主健1 = '1'
start with exists( select 1 from b where 港code= a.开始港code or 港code=a.终了港code)
connect by prior a.终了港code=a.开始港code
order by a.主健2;
select 1 id1,1 id2,'001' scode,'002' ecode from dual
union all
select 1 id1,2 id2,'002' scode,'003' ecode from dual
union all
select 1 id1,3 id2,'003' scode,'004' ecode from dual
),
b as(
select '001' gcode,'CN' ccode from dual
union all
select '002' gcode,'JP' ccode from dual
union all
select '003' gcode,'US' ccode from dual
union all
select '004' gcode,'AU' ccode from dual
)
,temp as(
select level lv from dual connect by level < 10
)
select wm_concat(ccode) from(
select distinct substr(street,instr(street,',',1,lv)-3,3) kk from(
select ltrim(sys_connect_by_path(scode,',')||','||ecode,',') street from a
where CONNECT_by_isleaf=1
start with id2 = 1
connect by prior ecode = scode
),temp order by kk
),b where gcode = kk
--测试通过with a as(
select 1 id1,1 id2,'001' scode,'002' ecode from dual
union all
select 1 id1,2 id2,'002' scode,'003' ecode from dual
union all
select 1 id1,3 id2,'003' scode,'004' ecode from dual
),
b as(
select '001' gcode,'CN' ccode from dual
union all
select '002' gcode,'JP' ccode from dual
union all
select '003' gcode,'US' ccode from dual
union all
select '004' gcode,'AU' ccode from dual
)
SELECT distinct b.ccode from b ,a
where a.id1 = 1
start with exists( select 1 from b where gcode= a.scode or gcode=a.ecode)
connect by prior a.ecode=a.scode;结果:
CODE
----------
AU
CN
JP
US
还有,实在不行,按minitoy思路,重新设计下表结构(不过估计这个也不行,因为原来设计的结构不是你想改就可以改的。)再想想
--试试:with a as(
select 1 id1,1 id2,'001' scode,'002' ecode from dual
union all
select 1 id1,2 id2,'002' scode,'003' ecode from dual
union all
select 1 id1,3 id2,'003' scode,'004' ecode from dual
),
b as(
select '001' gcode,'CN' ccode from dual
union all
select '002' gcode,'JP' ccode from dual
union all
select '003' gcode,'US' ccode from dual
union all
select '004' gcode,'AU' ccode from dual
)
SELECT ccode FROM b WHERE EXISTS(SELECT 1 FROM a WHERE b.gcode=scode OR b.gcode=ecode);
start with a.scode is not null
connect by prior ecode=scode
gelyon取得是所有的顶级节点,太多了.按phonix的,start with id=1试试.
start with 那里是取递归开始条件的.
select distinct B.国code from test,B
where test.code=B.code