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？
#1 楼
我同意这是一个非常糟糕的设计。如果无法更改该设计，请尝试以下操作：
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;
OUPUT
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;
如果您希望采用“ ANSIer”方法，请使用CTE：
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;
我尝试了3种方法，它们具有30000行数据集并返回118104行，并得到以下平均结果：
我的递归方法：5秒
MT0方法：4秒
Mathguy方法：16秒
MT0递归方法no-regex：3.45秒
@Mathguy还用更大的数据集进行了测试：
在所有情况下，递归查询（我只用常规的
substr和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 100：160 / 81.
10,000 x 200：1,924 / 772
评论
例如，请参见此内容，以了解如何避免重复（因此不必选择DISTINCT）：community.oracle.com/thread/2526535
–数学家
16年7月14日在11:47
伟大的见解@mathguy。谢谢。
–vercelli
16年7月14日在11:54
@vercelli请注意，这是一个hack。使用相关的表集合表达式（根据我的答案或此答案）不会生成重复项，也不需要此技巧即可防止数据中的循环连接。
– MT0
16年7月14日在12:08
@Vercelli-性能上的差异将是更好的理由选择“ collections”路由（MT0）和“递归”路由。我将进行更多测试-我怀疑更长的字符串（每个输入字符串具有更多的标记）的性能可能会有所不同。如果有兴趣，我可能应该打开一个单独的问题/线程以发布测试结果，等等。就目前而言，我刚刚测试了具有30000个输入字符串和每个字符串4个标记的分层解决方案（“我的”解决方案），总输出为120000行;在我的笔记本电脑上以及免费版本的Oracle 11.2中，运行需要5.1秒。
–数学家
16年7月14日在17:40
好的，我只是在不同的情况下进行了测试。在所有情况下，递归查询（我只测试了带有常规substr和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
–数学家
16年7月14日在19:26
#2 楼
这将获得值，而无需您删除重复项或必须使用在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;
选项2-使用
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（）中断周期时，我看不出黑客是什么。那是对分层查询的完全合法使用。
–数学家
16年7月14日在12:28
@mathguy汤姆·凯特（Tom Kyte）表示，这是文档中的一个错误，并且“不，您永远不需要在连接之前具有优先级”。 （链接）。
– MT0
16年7月14日在12:46
@ MT0，您在TRIM（REGEXP_SUBSTR（t.value，'[^，] +
–vercelli
16年7月14日在14:28
@vercelli谢谢，固定
– MT0
16年7月14日在14:58
只是阅读有关cast（multiset（....））的信息-如果我没记错的话（几个月前的阅读情况），Oracle中的嵌套表只能有一个列，对吗？还是有可能不仅提取令牌，还提取原始字符串中的令牌“级别”？在某些情况下，令牌的顺序可能具有在进一步处理中可能需要使用的含义。
–数学家
16年7月14日在17:37
#3 楼
韦尔切利（Vercelli）发布了正确答案。但是，如果要拆分多个字符串，则connect by将生成成倍增长的行数，其中包含许多重复项。 （只需尝试使用不带distinct的查询即可。）这将破坏非平凡数据的性能。克服此问题的一种常用方法是使用
prior条件和附加检查以避免循环。层次结构。像这样：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（）是一个hack，我相信最好使用关联表集合表达式，因为它永远不会生成这些重复项，因此您不必求助于变通方法来处理他们。
– MT0
16年7月14日在11:57
我们之前曾讨论过。我什么都不知道（我从今年2月才开始学习SQL和Oracle），但是我发现OTN，Tom Kyte等上的所有专家都使用sys_guid（）或dbms_random.value（）技巧。请参阅我提供的链接。请注意，使用PRIOR运算符无条件地进行连接已经是黑客（它违反了Oracle对CONNECT BY的要求-请参阅文档：docs.oracle.com/cd/B28359_01/server.111/b28286/queries003.htm，请参见语法图之后的第二个要点）。
–数学家
16年7月14日在12:04
@ MT0-实际上，我改变了主意。使用PRIOR在没有条件的情况下进行CONNECT BY是一种黑客行为，将来可能不受支持。我使用它的方式不是黑客，因为我确实在至少一种情况下使用PRIOR运算符。保证SYS_GUID（）为每一行产生不同的值，这将导致层次结构中没有循环。我不同意这是黑客。为什么是骇客？
–数学家
16年7月14日在12:24
这是一个hack，因为AND PRIOR SYS_GUID（）IS NOT NULL将始终为true，因此条件可以归结为AND TRUE，并且不相关-但是，删除它，您将得到ORA-01436：用户数据中的CONNECT BY循环。
– MT0
16年7月14日在12:34
那是不对的。 AND PRIOR SYS_GUID（）IS NOT NULL做两件事，而不是一件。在所有情况下，它的计算结果均为TRUE，但同时也会向每个新生成的行添加唯一的数据位。 AND TRUE仅执行第一项工作，而不执行第二项工作。
–数学家
16年7月14日在12:45
#4 楼
另一种方法是定义一个简单的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
#5 楼
--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;
评论
您可能需要添加解释。
–atline
12月7日9:01
评论
设计本身已经在我眼中出了问题。在一个列中存储多个值已经使规范化失败，因此不应该这样做。我宁愿在插入之前将它们拆分（或者不要使用单个值开头），并使用循环来插入单个值。可能重复的oracle-将oracle表中的多个逗号分隔值拆分为多行