今天看到篇博客,http://www.cnblogs.com/hankuikui/p/3523013.html,说的是按分隔符把一列转成多行,原文给出的方法是:
with tmp_t as (select '1001' as userId, '10,12,15' as workgroups from dual union select '1002' as userId, '2,4,5' as workgroups from dual) select userid, substr(tempgroups, instr(tempgroups, ',', 1, lv) + 1, instr(tempgroups, ',', 1, lv + 1) - (instr(tempgroups, ',', 1, lv) + 1)) from (select userid, ',' || workgroups || ',' AS tempgroups, length(workgroups || ',') - nvl(length(replace(workgroups, ',')) , 0) AS groupcount FROM tmp_t) a, (select LEVEL lv from dual CONNECT BY LEVEL <= 5) b where b.lv <= a.groupcount order by userid, lv
结果为:
使用了CONNECT BY 构造1-5的lv值和instr截取,详细分析见原文。
个人看到connect by level <=5总想这替换掉5,只要得到表中最大的,个数就可以了,如下:
with tmp_t as (select '1001' as userid, '10,12,15' as workgroups from dual union select '1002' as userid, '2,4,5' as workgroups from dual) select userid, substr(tempgroups, instr(tempgroups, ',', 1, lv) + 1, instr(tempgroups, ',', 1, lv + 1) - (instr(tempgroups, ',', 1, lv) + 1)) from (select userid, ',' || workgroups || ',' as tempgroups, length(workgroups || ',') - nvl(length(replace(workgroups, ',')) , 0) as groupcount from tmp_t) a, (select level lv from dual connect by level <= (select max(length(workgroups) - length(replace(workgroups, ',', ''))) + 1 from tmp_t)) b where b.lv <= a.groupcount order by userid, lv
上面的写法简单易懂,恰巧昨天看到篇文章,里面有更好的写法,原文链接:http://www.dewen.org/q/16184/%E4%BB%A5%E5%A4%8D%E6%9D%82%E6%95%B0%E6%8D%AE%E5%AD%97%E6%AE%B5%E5%88%86%E7%BB%84%E6%9F%A5%E8%AF%A2
更好的写法如下:
with tmp_t as (select '1001' as userid, '10,12,15' as workgroups from dual union select '1002' as userid, '2,4,5' as workgroups from dual) select userid, --regexp_substr(str,reg,起始位置 第几次) regexp_substr(workgroups, '[^,]+', 1, level) clr from tmp_t connect by level <= regexp_count(workgroups, ',') + 1 --regexp_count(workgroups, ',') 统计字符串中,的数量 --用法参考http://docs.oracle.com/cd/B28359_01/server.111/b28286/functions135.htm#SQLRF51665 and userid = prior userid and prior dbms_random.value is not null
相当简洁,很不错的写法,结果和上面的一样。
全文完。
相关推荐
oracle 某列存儲的值是有逗號的字符串,希望通過逗號分隔開來顯示多行。sql,有預期結果,有項目實戰。
不做详解,直接上图。 SELECT T.NAME, T.TESTSTR FROM TEST2 T; SELECT DISTINCT T.NAME, REPLACE(REGEXP_SUBSTR(T.TESTSTR, '[^,]+', 1, LEVEL), ',', ' ') TESTSTR FROM TEST2 T WHERE 1 = 1 ...
找了网上很多资料,都没有详细的做法有的还很复杂,自己写了个分割函数,通过直接查询就能分割多行,很方便。
代码中, 有编译链接的命令, 以及测试用的数据
oracle数据库一行拆成多行.sql
Oracle Sql语句转换成Mysql Sql语句java 源码,非常简单,只要给定源oracle sql语句地址,和生成目标文件地址运行即可。
sqlserver自动生成sql语句工具sqlserver转oracle
比如一个字段'11,22,22,33,44',一个查询语句显示成4行,就可以这样来实现。 11 22 22 33 44
Oracle 分割字符串 返回多行数据
介绍了将多行转为字符串的三种方案,并比较了三种方案的执行效率. 1.sys_connect_by_path + start with ... connect by ... prior + 分析函数 2.自定义Function/SP 3.使用 Oracle 10g 内置函数 wmsys.wm_concat
可将常规的sqlserver 的sql语句转换为 oracle语法,核心逻辑在SqlConvertToOracle.pas,便于入门。
连接sqlserver数据库后选择相应的数据库-数据表以及目标数据库(目前只有oracle),可以将sqlserver转换成oracle数据,有两种方式,点击转换sql按钮,可以生成sql语句,复制语句到oracle里执行。第二种是连接oracle...
oracle到sqlserver 存储过程语法转换,希望对大家有帮助啊! 最近刚把oracle的数据库移植到sqlserver!
oracle SQL查询工具oracle SQL查询工具oracle SQL查询工具oracle SQL查询工具oracle SQL查询工具oracle SQL查询工具oracle SQL查询工具oracle SQL查询工具oracle SQL查询工具oracle SQL查询工具oracle SQL查询工具...
oracle通过sql实现字符串转数字oracle函数,可用于字符串的最终排序
Oracle.10g.Pl.SQL编程Oracle.10g.Pl.SQL编程Oracle.10g.Pl.SQL编程Oracle.10g.Pl.SQL编程Oracle.10g.Pl.SQL编程Oracle.10g.Pl.SQL编程Oracle.10g.Pl.SQL编程Oracle.10g.Pl.SQL编程Oracle.10g.Pl.SQL编程Oracle.10g....
Oracle多行记录合并/连接/聚合字符串的几种方法
数据库 Mysql转oracle sql脚本转oracle脚本
sqlserver数据表向oracle转换,输入的参数是表名,输出的是建表语句
SqlServer表结构转oracle表结构,注释也会一同带上,界面操作可以对表可以单个选择多选查询等操作,C#开发源码见附件