一行数据当中有两个字段分别A和B 其中值存的内容是 A=32,25 B=12,13,14
actionid,action_note,a,b
119 火警 32,25 12,13,14变成
actionid,action_note,a,b
119 火警 32 12
119 火警 32 13
119 火警 32 14
119 火警 25 12
119 火警 25 13
119 火警 25 14
actionid,action_note,a,b
119 火警 32,25 12,13,14变成
actionid,action_note,a,b
119 火警 32 12
119 火警 32 13
119 火警 32 14
119 火警 25 12
119 火警 25 13
119 火警 25 14
解决方案 »
- oralce文本导入出问题 在线等!!!!大虾快救命呀
- system/sys下的表在sqlserver的dts中看不见
- 请教:刚装了oracle不太会用,想倒一个数据文件到数据库里,怎么做?谢谢!!!
- 一个简单问题,不过就是不行,解决了就给分!!!
- REDO01.LOG REDO02.LOG的内容是一样的吧?
- 求救:为什么每次启动需要用SYSDBA登录重新连接方可?
- oracle initialization or shutdown in progress 求助!急。
- 如何得到B表里最接近A表最大的一条记录
- 奇怪的问题,MINUS运算的结果和预想的不一致。
- varchar和varchar2类型一样吗?
- 求twitter、饭否这样微博的数据库表结构设计和分析
- 求个SQL语句
--oracle 10g:with tab as (
selecT '119' actionid, '火警' action_note, '32,25' a ,'12,13,14' b from dual
)
--上面是测试数据,下面是SQL:
select distinct actionid,action_note,
regexp_substr(a,'[^,]+',1,level) as a ,
b
from(
select actionid,action_note,a,
regexp_substr(b,'[^,]+',1,level) as b
from tab
connect by
level<=length(b)-length(replace(b,',',''))+1
)
connect by
level<=length(a)-length(replace(a,',',''))+1
--result:
ACTIONID ACTION_NOTE A B
--------------------------------
119 火警 25 12
119 火警 25 13
119 火警 25 14
119 火警 32 12
119 火警 32 13
119 火警 32 14
CREATE OR REPLACE FUNCTION splitstr(p_string IN VARCHAR2, p_delimiter IN VARCHAR2)
RETURN str_split
PIPELINED
AS
v_length NUMBER := LENGTH(p_string);
v_start NUMBER := 1;
v_index NUMBER;
BEGIN
WHILE(v_start <= v_length)
LOOP
v_index := INSTR(p_string, p_delimiter, v_start); IF v_index = 0
THEN
PIPE ROW(SUBSTR(p_string, v_start));
v_start := v_length + 1;
ELSE
PIPE ROW(SUBSTR(p_string, v_start, v_index - v_start));
v_start := v_index + 1;
END IF;
END LOOP; RETURN;
END splitstr;
第二步,处理数据:select 119, '火警', a.*, b.*
from table (select splitstr(a, ',') from tmp_jz where tid = 1) a,
table (select splitstr(b, ',') from tmp_jz where tid = 1) b;
呵呵,是我不好意思,我本来想说的是 gelyon 他是牛人,用正则表达式,没别的意思
点引用的时候点错了
119 火警 25 12
119 火警 25 13
119 火警 25 14
119 火警 32 12
119 火警 32 13
119 火警 32 14
110 警察 32 14
120 救护 32 12
with tab as (
selecT '119' actionid, '火警' action_note, '32,25' a ,'12,13,14' b from dual
)select t1.actionid,t1.action_note,t2.a,t1.b from (select actionid,action_note,
regexp_substr(b,'[^,]+',1,level) as b
from tab
connect by
level<=length(b)-length(replace(b,',',''))+1) t1,
(select actionid,
regexp_substr(a,'[^,]+',1,level) as a
from tab
connect by
level<=length(a)-length(replace(a,',',''))+1) t2 where t1.actionid=t2.actionid
ACTIONID ACTION_NOTE A B
-------- ----------- ---------- ----------------
119 火警 32 14
119 火警 32 13
119 火警 32 12
119 火警 25 14
119 火警 25 13
119 火警 25 12
create table OSS_DICTION_TEST_channel as
select distinct action_id,action_note,cate,thingid,
regexp_substr(moduie_id,'[^,]+',1,level) as moduie_id,channel_id
from
(
select action_id,action_note,cate,thingid,moduie_id ,channel_id
from OSS_DICTION_TEST
where channel_id like '%,%'
or channel_id like '%,%'
order by action_Id
)
connect by
level<=length(moduie_id)-length(replace(moduie_id,',',''))+1select * from OSS_DICTION_TEST_channel
create table OSS_DICTION_TEST_module as
select distinct action_id,action_note,cate,thingid,
moduie_id,
regexp_substr(channel_id,'[^,]+',1,level) as channel_id
from OSS_DICTION_TEST_channel
connect by
level<=length(channel_id)-length(replace(channel_id,',',''))+1select * from OSS_DICTION_TEST_module