distinct 를 사용하지 않고

[원쿼리맨]

같은 문제인데, distinct 를 사용하지 않고 만들어 봤습니다.


WITH table1 AS
     (SELECT 1 c1, 'd2#d3#d4#d4#d6' c2
        FROM DUAL
      UNION ALL
      SELECT 2, 'a2#s3#s4#f4#h6#g4#j6#u8'
        FROM DUAL)
SELECT   c1,
         SUBSTR (str,
                 INSTR (str, base, 1, level#) + len,
                   INSTR (str, base, 1, level# + 1)
                 - INSTR (str, base, 1, level#)
                 - len
                ) c2
    FROM (SELECT c1, '#' base, '#' || c2 || '#' str, LENGTH ('#') len, level#
            FROM table1 a,
                 (SELECT     LEVEL level#
                        FROM (SELECT MAX ((  LENGTH (c2)
                                           - LENGTH (REPLACE (c2, '#'))
                                           + 1
                                          )
                                         ) max#
                                FROM table1) t
                  CONNECT BY LEVEL <= t.max#) b
           WHERE (LENGTH (a.c2) - LENGTH (REPLACE (a.c2, '#')) + 1) >=
                                                                      b.level#)
ORDER BY c1, level#



C1   C2
-------
1    d2
1    d3
1    d4  -- 중복 허용
1    d4  -- 중복 허용
1    d6
2    a2
2    s3
2    s4
2    f4
2    h6
2    g4
2    j6
2    u8