Oracle - 字符串组合排列

2021-12-24 00:00:00 sql oracle oracle10g plsql

我想我有一个复杂的要求.

I think I have a complex requirement.

这是一个使用 Oracle 10.2 的组合排列，我能够使用笛卡尔连接解决它，但我认为它需要一些改进以使其更简单和更灵活.

It's a combinatorial permutation using Oracle 10.2, I'was able to solve it using cartesian joins, but I think that it need some improvements to made it simplest and more flexible.

主要行为.

输入字符串:'一二'

输出:'一''二''一二'两个一"

output: 'one' 'two' 'one two' 'two one'

对于我的解决方案，我将字符串的数量限制为 5(请注意，输出是阶乘附近的数字)

For my solution I've restricted the number of strings to 5 (note that the output is a number near the factorial)

SQL:

with My_Input_String as (select 1 as str_id, 'alpha beta omega gama' as str from dual )

--------logic-------

, String_Parse as (
                    SELECT REGEXP_SUBSTR(str, '[^ ]+', 1, ROWNUM) str
                    FROM My_Input_String
                    where rownum < 6 -- string limitation --
                    CONNECT BY level <= LENGTH(REGEXP_REPLACE(str, '([^ ])+|.', '1') ) 
                  )    

--------CRAP select need refactoring-------

select str from String_Parse
union
select  REGEXP_REPLACE(trim(s1.str||' '||s2.str||' '||s3.str||' '||s4.str||' '||s5.str), '( ){2,}', ' ') as str
from 

(select str from String_Parse union select ' ' from dual) s1,
(select str from String_Parse union select '  ' from dual) s2,
(select str from String_Parse union select '   ' from dual) s3,
(select str from String_Parse union select '    ' from dual) s4,
(select str from String_Parse union select '     ' from dual) s5
where 
--
s1.str <> s2.str and s1.str <> s3.str and s1.str <> s4.str and s1.str <> s5.str
--
and s2.str <> s3.str and s2.str <> s4.str and s2.str <> s5.str
--
and s3.str <> s4.str and s3.str <> s5.str
--
and s4.str <> s5.str

推荐答案

得到了通用的.最后真的很简单(但我花了一段时间才到达那里)

Got the generic one. Really simple in the end (but took me a while to get there)

WITH words AS
(   SELECT  REGEXP_SUBSTR( '&txt', 'S+', 1, LEVEL )    AS word
        ,   LEVEL                                       AS num
    FROM    DUAL
    CONNECT BY LEVEL <= LENGTH( REGEXP_REPLACE( '&txt', 'S+s*', 'X' ) )
)
SELECT  SYS_CONNECT_BY_PATH( W.word, ' ' )
FROM    words   W
CONNECT BY NOCYCLE PRIOR W.num != W.num

Edit2:删除了多余的 maxnum 内容.之前尝试的遗留问题

Removed redundant maxnum stuff. Left over from previous attempts

相关文章