这是DDL --
create table tbl1 (
id number,
value varchar2(50)
);
insert into tbl1 values (1, 'AA, UT, BT, SK, SX');
insert into tbl1 values (2, 'AA, UT, SX');
insert into tbl1 values (3, 'UT, SK, SX, ZF');
请注意，这里的值是逗号分隔的字符串。
但是，我们需要如下的结果-
ID VALUE
-------------
1 AA
1 UT
1 BT
1 SK
1 SX
2 AA
2 UT
2 SX
3 UT
3 SK
3 SX
3 ZF
我们该如何编写针对这个的SQL语句？
我同意这是一个非常糟糕的设计。 如果您不能更改该设计，请尝试以下方法：
select distinct id, trim(regexp_substr(value,'[^,]+', 1, level) ) value, level
from tbl1
connect by regexp_substr(value, '[^,]+', 1, level) is not null
order by id, level;
输出
id value level
1 AA 1
1 UT 2
1 BT 3
1 SK 4
1 SX 5
2 AA 1
2 UT 2
2 SX 3
3 UT 1
3 SK 2
3 SX 3
3 ZF 4
感谢这篇文章
为了更优雅和高效地去除重复项（感谢@mathguy）
select id, trim(regexp_substr(value,'[^,]+', 1, level) ) value, level
from tbl1
connect by regexp_substr(value, '[^,]+', 1, level) is not null
and PRIOR id = id
and PRIOR SYS_GUID() is not null
order by id, level;
with t (id,res,val,lev) as (
select id, trim(regexp_substr(value,'[^,]+', 1, 1 )) res, value as val, 1 as lev
from tbl1
where regexp_substr(value, '[^,]+', 1, 1) is not null
union all
select id, trim(regexp_substr(val,'[^,]+', 1, lev+1) ) res, val, lev+1 as lev
from t
where regexp_substr(val, '[^,]+', 1, lev+1) is not null
)
select id, res,lev
from t
order by id, lev;
输出
id val lev
1 AA 1
1 UT 2
1 BT 3
1 SK 4
1 SX 5
2 AA 1
2 UT 2
2 SX 3
3 UT 1
3 SK 2
3 SX 3
3 ZF 4
另一种MT0的递归方法，但不使用正则表达式：
WITH t ( id, value, start_pos, end_pos ) AS
( SELECT id, value, 1, INSTR( value, ',' ) FROM tbl1
UNION ALL
SELECT id,
value,
end_pos + 1,
INSTR( value, ',', end_pos + 1 )
FROM t
WHERE end_pos > 0
)
SELECT id,
SUBSTR( value, start_pos, DECODE( end_pos, 0, LENGTH( value ) + 1, end_pos ) - start_pos ) AS value
FROM t
ORDER BY id,
start_pos;
我已经尝试了三种方法，使用了一个30000行的数据集和返回了118104行，并得到了以下平均结果：
@Mathguy也测试了更大的数据集：
在所有情况下，递归查询（我只测试了具有常规substr和instr的查询）效果更好，因子为2到5。这里是字符串/每个字符串的标记数和层次结构与递归执行时间的组合，层次结构排在前面。所有时间都以秒为单位。
select DISTINCT）：https://community.oracle.com/thread/2526535 - user5683823substr 和 instr 的查询）表现更好，因为它的效率是层次结构查询的2到5倍。以下是每个字符串/每个字符串标记数量和层次结构与递归查询的CTAS执行时间组合，其中层次结构排在前面。所有时间以秒为单位。 30,000 x 4: 5 / 1. 30,000 x 10: 15 / 3. 30,000 x 25: 56 / 37. 5,000 x 50: 33 / 14. 5,000 x 100: 160 / 81. 10,000 x 200: 1,924 / 772 - user5683823这将获取值，无需您删除重复项或使用包含SYS_GUID()或DBMS_RANDOM.VALUE()在CONNECT BY中的技巧：
SELECT t.id,
v.COLUMN_VALUE AS value
FROM TBL1 t,
TABLE(
CAST(
MULTISET(
SELECT TRIM( REGEXP_SUBSTR( t.value, '[^,]+', 1, LEVEL ) )
FROM DUAL
CONNECT BY LEVEL <= REGEXP_COUNT( t.value, '[^,]+' )
)
AS SYS.ODCIVARCHAR2LIST
)
) v
更新:
返回列表中元素的索引：
选项1 - 返回UDT：
CREATE TYPE string_pair IS OBJECT( lvl INT, value VARCHAR2(4000) );
/
CREATE TYPE string_pair_table IS TABLE OF string_pair;
/
SELECT t.id,
v.*
FROM TBL1 t,
TABLE(
CAST(
MULTISET(
SELECT string_pair( level, TRIM( REGEXP_SUBSTR( t.value, '[^,]+', 1, LEVEL ) ) )
FROM DUAL
CONNECT BY LEVEL <= REGEXP_COUNT( t.value, '[^,]+' )
)
AS string_pair_table
)
) v;
ROW_NUMBER()：SELECT t.id,
v.COLUMN_VALUE AS value,
ROW_NUMBER() OVER ( PARTITION BY id ORDER BY ROWNUM ) AS lvl
FROM TBL1 t,
TABLE(
CAST(
MULTISET(
SELECT TRIM( REGEXP_SUBSTR( t.value, '[^,]+', 1, LEVEL ) )
FROM DUAL
CONNECT BY LEVEL <= REGEXP_COUNT( t.value, '[^,]+' )
)
AS SYS.ODCIVARCHAR2LIST
)
) v;
PRIOR运算符而没有条件的CONNECT BY，因为这是一种hack（它违反了Oracle对分层查询的要求）。但是，如果使用PRIOR SYS_GUID()来打破循环，我不认为这是一种hack；这是分层查询的完全合法用途。 - user5683823PRIOR。”（链接） - MT0ROW_NUMBER()分析函数。这只是我立刻想到的两种方法。 - MT0cast(multiset(...)) 方法（如果我可以弄清楚的话，我相信你已经发布过了）。谢谢！ - user5683823Vercelli发表了正确的答案。然而，如果有多个字符串要拆分，connect by将生成指数级增长的行数，并且会有很多重复行。（只需尝试一下没有distinct的查询即可。）对于非常规模的数据，这将破坏性能。
克服这个问题的一种常见方法是使用条件和额外的检查来避免层次结构中的循环。像这样：
select id, trim(regexp_substr(value,'[^,]+', 1, level) ) value, level
from tbl1
connect by regexp_substr(value, '[^,]+', 1, level) is not null
and prior id = id
and prior sys_guid() is not null
order by id, level;
例如，可以看看这个在OTN上的讨论：https://community.oracle.com/thread/2526535
SYS_GUID() 是一种不正规的方法，我认为最好使用相关表集合表达式来避免生成重复值，这样你就不必采取解决方法来处理它们。 - MT0sys_guid() 或 dbms_random.value()技巧。请查看我提供的链接。请注意connect by没有使用PRIOR运算符的条件已经是一种技巧了（它违反了Oracle对于CONNECT BY的要求-请参见文档：https：//docs.oracle.com/cd/B28359_01/server.111/b28286/queries003.htm，在语法说明后的第二个要点）。 - user5683823PRIOR的CONNECT BY而没有条件是一种hack，未来可能不被支持。我使用的方式不是hack，因为我至少在一个条件中使用了PRIOR运算符。SYS_GUID()保证为每一行生成不同的值，从而导致层次结构中没有循环。我不认为这是一种hack。为什么它是一种hack？ - user5683823AND PRIOR SYS_GUID() IS NOT NULL将始终为真，所以条件可以简化为AND TRUE，并且应该是无关紧要的 - 但是，如果删除它，你将得到ORA-01436: CONNECT BY loop in user data。 - MT0AND PRIOR SYS_GUID() IS NOT NULL 做了两件事，而不是一件事。它在所有情况下都评估为TRUE，但它还会为每个新生成的行添加一个唯一的数据位。 AND TRUE 只完成第一个任务，而不是第二个任务。 - user5683823另一种方法是定义一个简单的PL/SQL函数：
CREATE OR REPLACE FUNCTION split_String(
i_str IN VARCHAR2,
i_delim IN VARCHAR2 DEFAULT ','
) RETURN SYS.ODCIVARCHAR2LIST DETERMINISTIC
AS
p_result SYS.ODCIVARCHAR2LIST := SYS.ODCIVARCHAR2LIST();
p_start NUMBER(5) := 1;
p_end NUMBER(5);
c_len CONSTANT NUMBER(5) := LENGTH( i_str );
c_ld CONSTANT NUMBER(5) := LENGTH( i_delim );
BEGIN
IF c_len > 0 THEN
p_end := INSTR( i_str, i_delim, p_start );
WHILE p_end > 0 LOOP
p_result.EXTEND;
p_result( p_result.COUNT ) := SUBSTR( i_str, p_start, p_end - p_start );
p_start := p_end + c_ld;
p_end := INSTR( i_str, i_delim, p_start );
END LOOP;
IF p_start <= c_len + 1 THEN
p_result.EXTEND;
p_result( p_result.COUNT ) := SUBSTR( i_str, p_start, c_len - p_start + 1 );
END IF;
END IF;
RETURN p_result;
END;
/
那么SQL语句就变得非常简单：
SELECT t.id,
v.column_value AS value
FROM TBL1 t,
TABLE( split_String( t.value ) ) v
--converting row of data into comma sepaerated string
SELECT
department_id,
LISTAGG(first_name, ',') WITHIN GROUP(
ORDER BY
first_name
) comma_separted_data
FROM
hr.employees
GROUP BY
department_id;
--comma-separated string into row of data
CREATE TABLE t (
deptno NUMBER,
employee_name VARCHAR2(255)
);
INSERT INTO t VALUES (
10,
'mohan,sam,john'
);
INSERT INTO t VALUES (
20,
'manideeep,ashok,uma'
);
INSERT INTO t VALUES (
30,
'gopal,gopi,manoj'
);
SELECT
deptno,
employee_name,
regexp_count(employee_name, ',') + 1,
regexp_substr(employee_name, '\w+', 1, 1)
FROM
t,
LATERAL (
SELECT
level l
FROM
dual
CONNECT BY
level < regexp_count(employee_name, ',') + 1
);
DROP TABLE t;
SELECT COL1, COL2
FROM ( SELECT INDX, MY_STR1, MY_STR2, COL1_ELEMENTS, COL1, COL2_ELEMENTS, COL2
FROM ( SELECT 0 "INDX", COL1 "MY_STR1", COL1_ELEMENTS, COL1, '' "MY_STR2", COL2_ELEMENTS, COL2
FROM(
SELECT
REPLACE(COL1, ', ', ',') "COL1", -- In case there is a space after comma
Trim(Length(Replace(COL1, ' ', ''))) - Trim(Length(Translate(REPLACE(COL1, ', ', ','), 'A,', 'A'))) + 1 "COL1_ELEMENTS", -- Number of elements
Replace(COL2, ', ', ',') "COL2", -- In case there is a space after comma
Trim(Length(Replace(COL2, ' ', ''))) - Trim(Length(Translate(REPLACE(COL2, ', ', ','), 'A,', 'A'))) + 1 "COL2_ELEMENTS" -- Number of elements
FROM
(SELECT 'aaa,bbb,ccc' "COL1", 'qq, ww, ee' "COL2" FROM DUAL) -- Your example data
)
)
MODEL -- Modeling --> INDX = 0 COL1='aaa,bbb,ccc' COL2='qq,ww,ee'
DIMENSION BY(0 as INDX)
MEASURES(COL1, COL1_ELEMENTS, COL2, CAST('a' as VarChar2(4000)) as MY_STR1, CAST('a' as VarChar2(4000)) as MY_STR2)
RULES ITERATE (10) --UNTIL (ITERATION_NUMBER <= COL1_ELEMENTS[ITERATION_NUMBER + 1]) -- If you don't know the number of elements this should be bigger then you aproximation. Othewrwise it will split given number of elements
(
COL1_ELEMENTS[ITERATION_NUMBER + 1] = COL1_ELEMENTS[0],
MY_STR1[0] = COL1[CV()],
MY_STR1[ITERATION_NUMBER + 1] = SubStr(MY_STR1[ITERATION_NUMBER], InStr(MY_STR1[ITERATION_NUMBER], ',', 1) + 1),
COL1[ITERATION_NUMBER + 1] = SubStr(MY_STR1[ITERATION_NUMBER], 1, CASE WHEN InStr(MY_STR1[ITERATION_NUMBER], ',') <> 0 THEN InStr(MY_STR1[ITERATION_NUMBER], ',')-1 ELSE Length(MY_STR1[ITERATION_NUMBER]) END),
MY_STR2[0] = COL2[CV()],
MY_STR2[ITERATION_NUMBER + 1] = SubStr(MY_STR2[ITERATION_NUMBER], InStr(MY_STR2[ITERATION_NUMBER], ',', 1) + 1),
COL2[ITERATION_NUMBER + 1] = SubStr(MY_STR2[ITERATION_NUMBER], 1, CASE WHEN InStr(MY_STR2[ITERATION_NUMBER], ',') <> 0 THEN InStr(MY_STR2[ITERATION_NUMBER], ',')-1 ELSE Length(MY_STR2[ITERATION_NUMBER]) END)
)
)
WHERE INDX > 0 And INDX <= COL1_ELEMENTS -- INDX 0 contains starting strings
--
-- COL1 COL2
-- ---- ----
-- aaa qq
-- bbb ww
-- ccc ee