臭长：去除以逗号分隔的字符串的重复值--西林@生存　-　骑猪闯天下！

臭长：去除以逗号分隔的字符串的重复值

2007-1-5 21:41:00 By:xling

中午就看这个题了，只是第一反应是：这题不算很难，要有谁能用一句SQL能把它实现就更NB了！

下午又看这题，心里痒痒，就试着写了一下，还好，我还是奈着性子把它给写下来了。就当学习了吧！

要求：

如:'1,1,1,2,2,2,3,3,4,5,6,7,8,9,123,234,......'去除重复值1,2,3等.
效果为'1,2,3,4,5,6,7,8,9,123,234,....'

注，不是表，是一个字符串！

贴出我写SQL语句，当然如果用存储过程很容易解决，还好理解，不过吗...俺就用一句SQL来实现它！

SELECT * FROM (
    SELECT
        FF.TTT,
        FF.LEN
    FROM(
        SELECT
            EE.TT,
            EE.RN,
            EE.RNN,
            SYS_CONNECT_BY_PATH(EE.TT,',') AS TTT,
            LENGTH(SYS_CONNECT_BY_PATH(EE.TT,',')) AS LEN
        FROM (
            SELECT
                DD.TT,
                ROWNUM RN,
                ROWNUM + 1 AS RNN
            FROM (
                SELECT
                    CC.TT,CC.RN,
                    ROW_NUMBER() OVER (PARTITION BY CC.TT ORDER BY CC.RN) AS RNN
                FROM (
                    SELECT TT,ROWNUM AS RN FROM(
                        SELECT
                            SUBSTR(VAL,
                                   INSTR(AA.VAL,',',1,ROWNUM) + 1,
                                   INSTR(AA.VAL,',',1,ROWNUM + 1) - INSTR(AA.VAL,',',1,ROWNUM) - 1
                            ) AS TT
                        FROM (
                            SELECT ',' || 'AA,AA,CC,BB,CC,AA,DD,CC,FF,CC' || ',' AS VAL FROM DUAL
                        ) AA
                        CONNECT BY ROWNUM < LENGTH(AA.VAL)
                    ) BB
                    WHERE BB.TT IS NOT NULL
                ) CC
                ORDER BY CC.RN
            ) DD
            WHERE DD.RNN = 1
        )EE
        CONNECT BY PRIOR RNN = RN
    )FF
    ORDER BY LEN DESC
) GG
WHERE ROWNUM = 1