-- 支持多行SQL> SQL> create table test(id int, name varchar(50)); Table created SQL> begin 2 insert into test values(1, 'A,B,C,D,E,F'); 3 insert into test values(2, '111,333,555,22'); 4 end; 5 / PL/SQL procedure successfully completed SQL> col id format a10; SQL> select id, regexp_substr(name, '[^,]+', 1, level) colA 2 from test 3 connect by level <= regexp_count(name, ',') + 1 4 and prior rowid = rowid 5 and prior dbms_random.value is not null 6 order by id, cola; ID COLA ---------- ----------------------------------------- 1 A 1 B 1 C 1 D 1 E 1 F 2 111 2 22 2 333 2 555 10 rows selected SQL> drop table test purge; Table droppedSQL>
如果不带主键 id 那么 至单独查询 name值进行分组 是不是也可以。但是效率会很慢。
有一个工作表 A 其中有个字段 name 是一个工作几个人干(主键gid)。还有个工作情况表 B(主键qid,外键gid)。A和B是一对多的关系。现在需要查询出每个人都干了哪些工作。查询A表的每个人以及B表的gzm(工作名),gzxq(工作详情)。
CREATE OR REPLACE FUNCTION fn_split (p_str IN VARCHAR2, p_delimiter IN VARCHAR2) RETURN ty_str_split IS j INT := 0; i INT := 1; len INT := 0; len1 INT := 0; str VARCHAR2 (400); str_split ty_str_split := ty_str_split (); BEGIN len := LENGTH (p_str); len1 := LENGTH (p_delimiter);
WHILE j < len LOOP j := INSTR (p_str, p_delimiter, i);
IF j = 0 THEN j := len; str := SUBSTR (p_str, i); str_split.EXTEND; str_split (str_split.COUNT) := str;
IF i >= len THEN EXIT; END IF; ELSE str := SUBSTR (p_str, i, j - i); i := j + len1; str_split.EXTEND; str_split (str_split.COUNT) := str; END IF; END LOOP;
RETURN str_split; END fn_split; 创建一下拆分函数,绕后使用; 拆分本来就是低效率的,可以把拆分数据放入一个临时表中;然后再进行其他关联什么的;
-- 支持多行SQL>
SQL> create table test(id int, name varchar(50));
Table created
SQL> begin
2 insert into test values(1, 'A,B,C,D,E,F');
3 insert into test values(2, '111,333,555,22');
4 end;
5 /
PL/SQL procedure successfully completed
SQL> col id format a10;
SQL> select id, regexp_substr(name, '[^,]+', 1, level) colA
2 from test
3 connect by level <= regexp_count(name, ',') + 1
4 and prior rowid = rowid
5 and prior dbms_random.value is not null
6 order by id, cola;
ID COLA
---------- -----------------------------------------
1 A
1 B
1 C
1 D
1 E
1 F
2 111
2 22
2 333
2 555
10 rows selected
SQL> drop table test purge;
Table droppedSQL>
RETURN ty_str_split
IS
j INT := 0;
i INT := 1;
len INT := 0;
len1 INT := 0;
str VARCHAR2 (400);
str_split ty_str_split := ty_str_split ();
BEGIN
len := LENGTH (p_str);
len1 := LENGTH (p_delimiter);
WHILE j < len
LOOP
j := INSTR (p_str, p_delimiter, i);
IF j = 0
THEN
j := len;
str := SUBSTR (p_str, i);
str_split.EXTEND;
str_split (str_split.COUNT) := str;
IF i >= len
THEN
EXIT;
END IF;
ELSE
str := SUBSTR (p_str, i, j - i);
i := j + len1;
str_split.EXTEND;
str_split (str_split.COUNT) := str;
END IF;
END LOOP;
RETURN str_split;
END fn_split;
创建一下拆分函数,绕后使用;
拆分本来就是低效率的,可以把拆分数据放入一个临时表中;然后再进行其他关联什么的;