Oracle以逗号分隔同一字段变成多列

 SELECT DISTINCT
T.ID,
REGEXP_SUBSTR (T.DEPTS, '[^,]+', 1, LEVEL) DEPT
FROM
VROADWORK_TROADWORKMAIN T CONNECT BY LEVEL <= LENGTH (T.DEPTS) - LENGTH (REPLACE(T.DEPTS, ',', '')) + 1
ORDER BY
T.ID;

T.DEPTS是要以逗号分隔的字段

 

方法一

SELECT DISTINCT
    F_STAFF_ID AS USER_ID,
    REGEXP_SUBSTR (F_STATION, '[^,]+', 1, LEVEL) as STATION_ID
FROM
    T_API_STAFF_POST CONNECT BY LEVEL <= LENGTH (F_STATION) - LENGTH (REPLACE(F_STATION, ',', '')) + 1
ORDER BY
    F_STAFF_ID;
 

方法二

 SELECT F_STAFF_ID as USER_ID,
       REGEXP_SUBSTR(F_STATION, '[^,]+', 1, lv) AS STATION_ID
  FROM T_API_STAFF_POST,
       (SELECT LEVEL lv FROM dual CONNECT BY LEVEL < 20) b
 WHERE b.lv <= REGEXP_COUNT(T_API_STAFF_POST.F_STATION, '\,') + 1
 ORDER BY F_STAFF_ID;

方法三

 SELECT F_STAFF_ID AS USER_ID,
       substr(a.F_STATION,
              instr(a.F_STATION, ',', 1, levels.lvl) + 1,
              instr(a.F_STATION, ',', 1, levels.lvl + 1) -
              (instr(a.F_STATION, ',', 1, levels.lvl) + 1)) as STATION_ID
  FROM (SELECT F_STAFF_ID,
               ',' || F_STATION || ',' AS F_STATION,
               length(F_STATION) - nvl(length(REPLACE(F_STATION, ',')), 0) + 1 AS cnt --取待拆分字段每行按照分隔符','分割后的记录数，用于connect by
          FROM T_API_STAFF_POST) a,
       (SELECT rownum AS lvl --产生一个待拆分字段分割后最大记录数的序列
          FROM (SELECT MAX(length(F_STATION || ',') -
                           nvl(length(REPLACE(F_STATION, ',')), 0)) max_len
                  FROM T_API_STAFF_POST)
        CONNECT BY LEVEL <= max_len) levels
 WHERE levels.lvl <= a.cnt --笛卡尔连接
 ORDER BY F_STAFF_ID;

性能上方法三最优，其次方法二，方法一因为有distinct，性能可想而知