横列转换

最新推荐文章于 2018-08-29 11:07:08 发布

Clam

最新推荐文章于 2018-08-29 11:07:08 发布

阅读量171

点赞数 1

本文链接：https://blog.youkuaiyun.com/yuanpan365/article/details/83844674

版权

(WITH t1 AS (select rtrim(user_id,',') str from dual)
    SELECT s from (SELECT substr(t1.str,instr(t1.str,',',1,LEVEL) + 1,
    decode(instr(t1.str,',',1, LEVEL + 1),0,length(str) + 1,
     instr(t1.str,',',1,LEVEL + 1)) - instr(t1.str,',',1,LEVEL) - 1) s
   FROM dual, t1 CONNECT BY LEVEL <= length(str) -length(REPLACE(str, ','))))

user_id为传入值如(',1001,1002,1003,1004,')

最后结构可以转换为

1001

1002

1003

1004