17,380
社区成员
发帖
与我相关
我的任务
分享请教如何用sql语句拆分记录？
大家好，
我现在有一个表：t_test,表结构：
id name items
1 aa a,b c
2 aaa c d,e
3 bbb d,e，g
我想把items中用“,”、“空格”、“全角，”隔开的字符串拆成单独的记录，用sql语句执行后想要这样的查询结果：
id name item
1 aa a
1 aa b
1 aa c
2 aaa c
2 aaa d
2 aaa e
3 bbb d
3 bbb e
3 bbb g
请问我如何用sql语句来处理，谢谢！
我现在有一个表：t_test,表结构：
id name items
1 aa a,b c
2 aaa c d,e
3 bbb d,e，g
我想把items中用“,”、“空格”、“全角，”隔开的字符串拆成单独的记录，用sql语句执行后想要这样的查询结果：
id name item
1 aa a
1 aa b
1 aa c
2 aaa c
2 aaa d
2 aaa e
3 bbb d
3 bbb e
3 bbb g
请问我如何用sql语句来处理，谢谢！
...全文
请发表友善的回复…
发表回复
jbas 2010-11-10
- 打赏
- 举报
不好意思，前段时间出差了，非常感谢各位大力帮忙。
minitoy 2010-11-04
- 打赏
- 举报
SQL> select * from t_test;
ID NAME ITEMS
---------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
1 aa aa,bb cc
2 aaa ccc ddd,eee
3 bb dds,efd，g
SQL>
SQL> with a as(select id,name,' '||f_convert(items)||' ' items from t_test),
2 b as(select rownum rn from dual connect by rownum<(select max(length(translate(items,' '||items,' ')))-1 from a))
3 select id,name,substr(items,instr(items,' ',1,rn)+1,instr(items,' ',1,rn+1)-instr(items,' ',1,rn)-1) items
4 from a,b ;
ID NAME ITEMS
---------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
1 aa aa
1 aa bb
1 aa cc
2 aaa ccc
2 aaa ddd
2 aaa eee
3 bb dds
3 bb efd
3 bb g
9 rows selected
SQL> minitoy 2010-11-04
- 打赏
- 举报
SQL> create or replace function f_convert(i_str in varchar2) return varchar2
2 as
3 char_now varchar2(10);
4 new_str varchar2(4000);
5 begin
6 new_str:='';
7 for i in 1..length(i_str) loop
8 char_now:=substr(i_str,i,1);
9 if (char_now in (' ',',') or to_single_byte(char_now)!=char_now) then
10 char_now:=' ';
11 end if;
12 if char_now!=' ' or (substr(new_str,-1,1)!=' ' and new_str is not null) then
13 new_str:=new_str||char_now;
14 end if;
15 end loop;
16 return trim(new_str);
17 end;
18 /
Function created
SQL> select * from t_test;
ID NAME ITEMS
---------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
1 aa a,b c
2 aaa c d,e
3 bb d,e，g
SQL> with a as(select id,name,' '||f_convert(items)||' ' items from t_test),
2 b as(select rownum rn from dual connect by rownum<(select max(length(translate(items,' '||items,' ')))-1 from a))
3 select id,name,substr(items,instr(items,' ',1,rn)+1,instr(items,' ',1,rn+1)-instr(items,' ',1,rn)-1) items
4 from a,b;
ID NAME ITEMS
---------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
1 aa a
1 aa b
1 aa c
2 aaa c
2 aaa e
2 aaa d
3 bb d
3 bb g
3 bb e
9 rows selected
SQL> minitoy 2010-11-04
- 打赏
- 举报
先利用下面函数把表里数据整理下再拆分.
SQL> create or replace function f_convert(i_str in varchar2) return varchar2
2 as
3 char_now char(1);
4 new_str varchar2(4000);
5 begin
6 new_str:='';
7 for i in 1..length(i_str) loop
8 char_now:=substr(i_str,i,1);
9 if (char_now in (' ',',') or to_single_byte(char_now)!=char_now) then
10 char_now:=' ';
11 end if;
12 if char_now!=' ' or (substr(new_str,-1,1)!=' ' and new_str is not null) then
13 new_str:=new_str||char_now;
14 end if;
15 end loop;
16 return new_str;
17 end;
18 /
Function created
SQL> select * from t_test;
ID NAME ITEMS
---------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
1 aa a,b c
2 aaa c d,e
3 bb d,e，g
SQL> select id,name,f_convert(items) items from t_test;
ID NAME ITEMS
---------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
1 aa a b c
2 aaa c d e
3 bb d e g
SQL>
zkl516321905 2010-11-04
- 打赏
- 举报
两点分析步骤，1先把item字段记录分拆了，然后在用oracle的层次查询，
minitoy 2010-11-04
- 打赏
- 举报
写函数吧.
真的不知道取什么名 2010-11-04
- 打赏
- 举报
[Quote=引用 1 楼 gelyon 的回复:]
SQL code
WITH tab AS(
SELECT 1 id,'aa' NAME,'a,b c' items FROM dual
UNION ALL
SELECT 2,'aaa','c d,e' FROM dual
UNION ALL
SELECT 3,'bbb', 'd,e，g' FROM dual
)
SELECT DISTINCT id,NAME,item FRO……
[/Quote]
SQL code
WITH tab AS(
SELECT 1 id,'aa' NAME,'a,b c' items FROM dual
UNION ALL
SELECT 2,'aaa','c d,e' FROM dual
UNION ALL
SELECT 3,'bbb', 'd,e，g' FROM dual
)
SELECT DISTINCT id,NAME,item FRO……
[/Quote]
gelyon 2010-11-04
- 打赏
- 举报
WITH tab AS(
SELECT 1 id,'aa' NAME,'a,b c' items FROM dual
UNION ALL
SELECT 2,'aaa','c d,e' FROM dual
UNION ALL
SELECT 3,'bbb', 'd,e，g' FROM dual
)
SELECT DISTINCT id,NAME,item FROM (
SELECT id,NAME,SubStr(items,LEVEL,1) item
FROM tab
CONNECT BY LEVEL<=Length(items)
)
WHERE REGEXP_LIKE(item, '[[:alpha:]]' )
结果：
ID NAME ITEM
------------------
1 aa a
1 aa b
1 aa c
2 aaa c
2 aaa d
2 aaa e
3 bbb d
3 bbb e
3 bbb g
gelyon 2010-11-04
- 打赏
- 举报
[Quote=引用 8 楼 onemetre 的回复:]
按照楼主描述要求
1.分割的是字符串，不是字符
2.看1#gelyon的SQL很精辟，但用了distinct，“a,a,a,b c”就有问题了
自己也试着写了个，可以试试。
SQL code
WITH T_TEST AS(
SELECT 1 ID,'name1' NAME,'str1,str2 str3' ITEMS FROM dual
UNION ALL
SELE……
[/Quote]
谢谢提醒并指出缺点，改善：增加项次level就可以了：
WITH tab AS(
SELECT 1 id,'aa' NAME,'a,a,b c' items FROM dual
UNION ALL
SELECT 2,'aaa','c d,e' FROM dual
UNION ALL
SELECT 3,'bbb', 'd,e，g' FROM dual
)
SELECT id,NAME,item FROM (
SELECT DISTINCT id,NAME,item ,levelno
FROM (
SELECT id,NAME,SubStr(items,LEVEL,1) item ,LEVEL levelno
FROM tab
CONNECT BY LEVEL<=Length(items)
)
WHERE REGEXP_LIKE(item, '[[:alpha:]]' )
)
ORDER BY 1,2,3
按照楼主描述要求
1.分割的是字符串，不是字符
2.看1#gelyon的SQL很精辟，但用了distinct，“a,a,a,b c”就有问题了
自己也试着写了个，可以试试。
SQL code
WITH T_TEST AS(
SELECT 1 ID,'name1' NAME,'str1,str2 str3' ITEMS FROM dual
UNION ALL
SELE……
[/Quote]
谢谢提醒并指出缺点，改善：增加项次level就可以了：
WITH tab AS(
SELECT 1 id,'aa' NAME,'a,a,b c' items FROM dual
UNION ALL
SELECT 2,'aaa','c d,e' FROM dual
UNION ALL
SELECT 3,'bbb', 'd,e，g' FROM dual
)
SELECT id,NAME,item FROM (
SELECT DISTINCT id,NAME,item ,levelno
FROM (
SELECT id,NAME,SubStr(items,LEVEL,1) item ,LEVEL levelno
FROM tab
CONNECT BY LEVEL<=Length(items)
)
WHERE REGEXP_LIKE(item, '[[:alpha:]]' )
)
ORDER BY 1,2,3
心中的彩虹 2010-11-04
- 打赏
- 举报
[Quote=引用楼主 jbas 的回复:]
大家好，
我现在有一个表：t_test,表结构：
id name items
1 aa a,b c
2 aaa c d,e
3 bbb d,e，g
我想把items中用“,”、“空格”、“全角，”隔开的字符串拆成单独的记录，用sql语句执行后想要这样的查询结果：
id name item
1 aa a
1 aa b
1 aa c
2 aaa c
2 aaa d
2……
[/Quote]
大家好，
我现在有一个表：t_test,表结构：
id name items
1 aa a,b c
2 aaa c d,e
3 bbb d,e，g
我想把items中用“,”、“空格”、“全角，”隔开的字符串拆成单独的记录，用sql语句执行后想要这样的查询结果：
id name item
1 aa a
1 aa b
1 aa c
2 aaa c
2 aaa d
2……
[/Quote]
with tb as(
select 1 id,'aa' NAME,'a,b c' items from dual
UNION ALL
select 2,'aaa','c d,e' from dual
UNION ALL
select 3,'bbb', 'd,e，g' from dual
)
select *
from (select id,name,substr(items,level,1) items
from tb connect by level<=length(items) and connect_by_root(name)=name)
where regexp_instr(items,'[[:alpha:]]+',1)>0
--结果
ID NAM IT
---------- --- --
1 aa a
1 aa b
1 aa c
2 aaa c
2 aaa d
2 aaa e
3 bbb d
3 bbb e
3 bbb g
已选择9行。
onemetre 2010-11-04
- 打赏
- 举报
按照楼主描述要求
1.分割的是字符串，不是字符
2.看1#gelyon的SQL很精辟，但用了distinct，“a,a,a,b c”就有问题了
自己也试着写了个，可以试试。
1.分割的是字符串，不是字符
2.看1#gelyon的SQL很精辟，但用了distinct，“a,a,a,b c”就有问题了
自己也试着写了个，可以试试。
WITH T_TEST AS(
SELECT 1 ID,'name1' NAME,'str1,str2 str3' ITEMS FROM dual
UNION ALL
SELECT 2,'name2',' str4,str5,str6 ' FROM dual
UNION ALL
SELECT 3,'name3', 'str7，str8 ' FROM dual
)
select ID ,NAME,regexp_substr(ITEMS,'\w+',1,level)
from (select ID,NAME,trim(regexp_replace(ITEMS,'[, ，]+',' ')) ITEMS from T_TEST )
connect by level<=length(regexp_replace(ITEMS,'\w+'))+1 and ID=connect_by_root(ID)
order by 1,2,3
