这种效果么?with A_CODE as
(
select 'A' DMZ,'北京' DMSM from dual union all
select 'B' DMZ,'上海' DMSM from dual union all
select 'C' DMZ,'天津' DMSM from dual union all
select 'D' DMZ,'山东' DMSM from dual
),A_MAIN as
(
select '张三' XM,'男' XB ,'ACD' ADDR from dual union all
select '李四' XM,'男' XB ,'B' ADDR from dual
)
select XM,wm_concat(DMSM) DMSM
from A_CODE a,
(
select distinct XM,substr(ADDR,level,1) ADDR
from A_MAIN
connect by level < length(ADDR)+1) b
where a.DMZ = b.ADDR
group by XM XM DMSM
-----------------------------------
1 李四 上海
2 张三 北京,山东,天津
(
select 'A' DMZ,'北京' DMSM from dual union all
select 'B' DMZ,'上海' DMSM from dual union all
select 'C' DMZ,'天津' DMSM from dual union all
select 'D' DMZ,'山东' DMSM from dual
),A_MAIN as
(
select '张三' XM,'男' XB ,'ACD' ADDR from dual union all
select '李四' XM,'男' XB ,'B' ADDR from dual
)
select XM,wm_concat(DMSM) DMSM
from A_CODE a,
(
select distinct XM,substr(ADDR,level,1) ADDR
from A_MAIN
connect by level < length(ADDR)+1) b
where a.DMZ = b.ADDR
group by XM XM DMSM
-----------------------------------
1 李四 上海
2 张三 北京,山东,天津
解决方案 »
- ORACLE存储过程的问题,新手求助,非常着急啊!!!!!!!!!!!
- 关于oracle 运行
- 如果实现记录合并
- 在线等待!!!急
- Oracle还是DB2?
- 初次接触oracle,请大家帮忙呀!! 在线等待
- CONNECT BY 超难问题!在线等....高手请进!
- ORACLE全文检索如何做到多条件查询???????????????
- 如何实现RNU A.SQL 后把它的结果集存入一个文本文件中
- 触发器问题
- 为什么会报(full) year must be between -4713 and +9999,and not be 0
- oracle使用order by和rownum效率很慢,有没有提升的办法?
select 'A' dmz, '北京' dmsm from dual union all
select 'B' dmz, '上海' dmsm from dual union all
select 'C' dmz, '天津' dmsm from dual union all
select 'D' dmz, '山东' dmsm from dual
), a_main as (
select '张三' xm, '男' xb, 'ACD' addr from dual union all
select '李四' xm, '男' xb, 'B' addr from dual)
select t.*, (select listagg(dmsm, ',') within group(order by 1) from a_code where instr(t.addr, dmz) > 1) las
from a_main t;
select 'A' dmz, '北京' dmsm from dual union all
select 'B' dmz, '上海' dmsm from dual union all
select 'C' dmz, '天津' dmsm from dual union all
select 'D' dmz, '山东' dmsm from dual
), a_main as (
select '张三' xm, '男' xb, 'ACD' addr from dual union all
select '李四' xm, '男' xb, 'B' addr from dual)
select t.*, (select listagg(dmsm, ',') within group(order by 1) from a_code where instr(t.addr, dmz) > 0) las
from a_main t;
抱歉,有个小问题
select '10118' dmlb, 'A' dmz, '白色' dmsm1 from dual union all
select '10118' dmlb, 'B' dmz, '黑色' dmsm1 from dual union all
select '10118' dmlb, 'C' dmz, '红色' dmsm1 from dual union all
select '10118' dmlb, 'D' dmz, '蓝色' dmsm1 from dual union all
select '10119' dmlb, 'A' dmz, '注销' dmsm1 from dual union all
select '10119' dmlb, 'B' dmz, '恢复' dmsm1 from dual)
,vehicle as (
select 'A88888' hphm, 'ABC' csys from dual union all
select 'A88889' hphm, 'BC' csys from dual)
select t.*,
(select listagg(dmsm1, ',') within group(order by 1) from frm_code where instr(t.csys, dmz) > 0 and dmlb = '10118') dmsm1s
from vehicle t where hphm = 'A88888';
select b.*,a.dmsm from A_MAIN b,A_CODE a where b.addr like '%'||a.dmz||'%'
经过调整之后: select amain.* ,ade.dmsm from A_MAIN amain join
(select acode.dmsm from A_CODE acode where (select addr from A_MAIN where xm='张三') like '%'||acode.dmz||'%') ade
on amain.xm='张三';附上截图