oracle逗号拆分多列,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，性能可想而知