我去,这分数又要浪费了 我搞定了create or replace type strsplit_type as table of VARCHAR2(32676)
/create or replace function strsplit(p_value varchar2,
p_split varchar2 := ',')
--usage: select * from table(strsplit('1,2,3,4,5',','))
return strsplit_type
pipelined is
v_idx integer;
v_str varchar2(500);
v_strs_last varchar2(4000) := p_value;begin
loop
v_idx := instr(v_strs_last, p_split);
exit when v_idx = 0;
v_str := substr(v_strs_last, 1, v_idx - 1);
v_strs_last := substr(v_strs_last, v_idx + 1);
pipe row(v_str);
end loop;
pipe row(v_strs_last);
return;end strsplit;
/--select * from table(strsplit((select a.name from a a),','))
/create or replace function strsplit(p_value varchar2,
p_split varchar2 := ',')
--usage: select * from table(strsplit('1,2,3,4,5',','))
return strsplit_type
pipelined is
v_idx integer;
v_str varchar2(500);
v_strs_last varchar2(4000) := p_value;begin
loop
v_idx := instr(v_strs_last, p_split);
exit when v_idx = 0;
v_str := substr(v_strs_last, 1, v_idx - 1);
v_strs_last := substr(v_strs_last, v_idx + 1);
pipe row(v_str);
end loop;
pipe row(v_strs_last);
return;end strsplit;
/--select * from table(strsplit((select a.name from a a),','))
等等....这是Oracle的 还有sqlserver的
select id,
REGEXP_SUBSTR(date, '[^,]+', 1, LEVEL) date
from test
CONNECT BY LEVEL <= REGEXP_COUNT(date, ',') + 1
and id= prior id
and rowid=prior rowid
and prior dbms_random.value is not null;
id一样多条数据也是可以啊
6 a
6 b
6 c
6 b
6 c
6 d
8 a
8 b
8 c
id一样多条数据也是可以啊
6 a
6 b
6 c
6 b
6 c
6 d
8 a
8 b
8 c如果是多条数据呢,而且id也不一样,请问怎么写
id一样多条数据也是可以啊
6 a
6 b
6 c
6 b
6 c
6 d
8 a
8 b
8 c
good,膜拜你大神