我对oracle拆分查询有问题。
在使用connect by和oracle中的正则表达式将逗号分隔成多个行时,我得到了更多的重复行。例如,实际上我的表有150行,其中两行有逗号分隔的字符串,所以总的来说,我只需要得到155行,但我要得到2000行。如果我使用不同,它的工作良好,但我不希望重复行的查询结果。
我尝试了以下查询,但是它在查询结果中生成重复行:
WITH CTE AS (SELECT 'a,b,c,d,e' temp,1 slno FROM DUAL
UNION
SELECT 'f,g',2 from dual
UNION
SELECT 'h',3 FROM DUAL)
SELECT TRIM(REGEXP_SUBSTR( TEMP, '[^,]+', 1, LEVEL)) ,SLNO FROM CTE
CONNECT BY LEVEL <= LENGTH(REGEXP_REPLACE(temp, '[^,]+')) + 1编辑
上面的select查询只能拆分单个逗号分隔的字符串,但是,当在具有多行的表上执行时,它会产生重复行。如何限制重复行?
发布于 2013-12-05 11:45:43
最后我想出了这个答案
WITH CTE AS (SELECT 'a,b,c,d,e' temp, 1 slno FROM DUAL
UNION
SELECT 'f,g' temp, 2 slno FROM DUAL
UNION
SELECT 'h' temp, 3 slno FROM DUAL)
SELECT TRIM(REGEXP_SUBSTR(temp, '[^,]+', 1, level)), slno
FROM CTE
CONNECT BY level <= REGEXP_COUNT(temp, '[^,]+')
AND PRIOR slno = slno
AND PRIOR DBMS_RANDOM.VALUE IS NOT NULL发布于 2013-09-12 18:23:40
就像这样,
WITH CTE AS (SELECT 'a,b,c,d,e' temp,1 slno FROM DUAL
UNION
SELECT 'f,g',2 from dual
UNION
SELECT 'h',3 FROM DUAL)
SELECT regexp_substr (temp, '[^,]+', 1, rn)temp, slno
FROM cte
CROSS JOIN
(
SELECT ROWNUM rn
FROM (SELECT MAX (LENGTH (regexp_replace (temp, '[^,]+'))) + 1 max_l
from cte
)
connect by level <= max_l
)
WHERE regexp_substr (temp, '[^,]+', 1, rn) IS NOT NULL
order by temp;发布于 2015-03-25 12:09:25
接受的答案使用条件DBMS_RANDOM.VALUE IS NOT NULL,这是不适当的。它只是防止循环循环,然而,一个直接的问题会出现在dbms_random.VALUE 如何以及何时可以是空的?逻辑上,它永远不会是空。
更合适的解决方案是使用sys.odciNumberList并防止循环循环。
例如,
设置
SQL> CREATE TABLE t (
2 ID NUMBER GENERATED ALWAYS AS IDENTITY,
3 text VARCHAR2(100)
4 );
Table created.
SQL>
SQL> INSERT INTO t (text) VALUES ('word1, word2, word3');
1 row created.
SQL> INSERT INTO t (text) VALUES ('word4, word5, word6');
1 row created.
SQL> INSERT INTO t (text) VALUES ('word7, word8, word9');
1 row created.
SQL> COMMIT;
Commit complete.
SQL>
SQL> SELECT * FROM t;
ID TEXT
---------- ----------------------------------------------
1 word1, word2, word3
2 word4, word5, word6
3 word7, word8, word9
SQL>必需查询:
SQL> SELECT t.id,
2 trim(regexp_substr(t.text, '[^,]+', 1, lines.column_value)) text
3 FROM t,
4 TABLE (CAST (MULTISET
5 (SELECT LEVEL FROM dual CONNECT BY LEVEL <= regexp_count(t.text, ',')+1)
6 AS sys.odciNumberList
7 )
8 ) lines
9 ORDER BY id
10 /
ID TEXT
---------- --------------------------------------------------
1 word1
1 word2
1 word3
2 word4
2 word5
2 word6
3 word7
3 word8
3 word9
9 rows selected.使用XMLTABLE**:** 的替代解决方案
SQL> SELECT id,
2 trim(COLUMN_VALUE) text
3 FROM t,
4 xmltable(('"'
5 || REPLACE(text, ',', '","')
6 || '"'))
7 /
ID TEXT
---------- ------------------------
1 word1
1 word2
1 word3
2 word4
2 word5
2 word6
3 word7
3 word8
3 word9
9 rows selected.
SQL>实现这一任务的方法有很多,比如模型子句。有关更多示例,请参见Split comma delimited strings in a table
https://stackoverflow.com/questions/18770581
复制相似问题