物流公司网站 源码,视频网站前台怎么做,linux 网站开发,怎么制作网站卖东西Sql进阶 一、问题描述二、解决思路一、拆成多行二、拆成多列 三、代码实现 一、问题描述
Oracle数据库中某个字段value是CLOB类型,存的是csv格式的数据,如下所示
classnovalue1name,age,sex,… ‘李世民’,20,‘M’,…’ ‘李治’,18,‘M’,… ‘武则天’,16… Sql进阶 一、问题描述二、解决思路一、拆成多行二、拆成多列 三、代码实现 一、问题描述
Oracle数据库中某个字段value是CLOB类型,存的是csv格式的数据,如下所示
classnovalue1name,age,sex,… ‘李世民’,20,‘M’,…’ ‘李治’,18,‘M’,… ‘武则天’,16,‘F’,… ‘李隆基’,14,‘M’,…2…
需要把上述clob类型的csv字段用Sql的方式展开,如上述csv字段有四行三列,就需要把上述字段转成实际的四行三列,如下所示
classnonameagesex…1李世民20M1李治18M1武则天16F1李隆基14M2………
二、解决思路
一、拆成多行
按照换行符拆分一个个的列表,上述换行符是\n,按照\n进行拆分比较难写,考虑先把\n替换成其它符号,如分号
换行符在oracle中用chr(10)表示
select replace(value,chr(10),;) as value from table按照换行符进行拆分字符串
select to_char(regexp_substr(value,[^;],1,level) as split_valuefrom table
connect by level regexp_count(value,[^;],1)and prior class_no class_noand prior sys_guid() is not nullregexp_substr()函数为拆分字符串,若没有connect by语句,只是
select to_char(regexp_substr(value[^;],1) as split_valuefrom table则不会循环进行拆分,只会拆分第一段,比如我那个例子,只会获取到
classnovalue1‘李世民’,20,‘M’2… CONNECT BY是Oracle SQL中的一个子句用于定义层次结构或递归关系从而进行层次结构数据的查询。 LEVEL是Oracle SQL中的一个伪列用于在层次结构或递归查询中获取当前行的级别。 REGEXP_COUNT 用于计算字符串中正则表达式匹配的次数 上述level regexp_count(value,‘[^;]’,1)就是递归停止的条件 prior条件指的是当前递归在哪个层级下运行,比如上述例子一个csv字段描述的是一个班级的事情,递归是在这个班级下运行,所以prior条件要加上prior class_no classno,不然会造成数据重复 需要注意prior后接的条件需要能够限制某个递归层级,不然可能会造成数据不断的循环 若是有多个prior条件,可以 and prior col1 col1 and prior col2 col2 而不是 and prior col1 col1 and col2 col2 经过上述处理之后,得到的结果应该是
classnovalue1name,age,sex,…1‘李世民’,20,‘M’,…1’ ‘李治’,18,‘M’,…1‘武则天’,16,‘F’,…1‘李隆基’,14,‘M’,2…
已经拆成多行了,剩下的是拆成多列
二、拆成多列
根据列的分隔符来拆分,以逗号为例
select regexp_substr(split_value,[^,],1,1) as name,regexp_substr(split_value,[^,],1,2) as age,regexp_substr(split_value,[^,],1,3) as sexfrom table还是用regexp_substr函数来拆分,只不过不进行递归查询,
三、代码实现
with tmp as (select classno,replace(value,chr(10),;) as valuefrom table
),tmp1 as (select to_char(regexp_substr(value,[^;],1,level)) as split_value,classnofrom tmpconnect by level regexp_count(value,[^;],1)and prior classno classnoand prior sys_guid() is not null
),tmp2 as (select classno,regexp_substr(value,[^,],1,1) as name,regexp_substr(value,[^,],1,2) as age,regexp_substr(value,[^,],1,3) as sexfrom tmp1
)select classno,name,age,sexfrom tmp2 where name ! name
