Sql进阶
- 一、问题描述
- 二、解决思路
- <一>、拆成多行
- <二>、拆成多列
- 三、代码实现
一、问题描述
Oracle数据库中某个字段value是CLOB类型,存的是csv格式的数据,如下所示
| classno | value |
|---|---|
| 1 | name,age,sex,… ‘李世民’,20,‘M’,…’ ‘李治’,18,‘M’,… ‘武则天’,16,‘F’,… ‘李隆基’,14,‘M’,… |
| 2 | … |
需要把上述clob类型的csv字段用Sql的方式展开,如上述csv字段有四行三列,就需要把上述字段转成实际的四行三列,如下所示
| classno | name | age | sex | … |
|---|---|---|---|---|
| 1 | 李世民 | 20 | M | |
| 1 | 李治 | 18 | M | |
| 1 | 武则天 | 16 | F | |
| 1 | 李隆基 | 14 | M | |
| 2 | … | … | … |
二、解决思路
<一>、拆成多行
- 按照换行符拆分一个个的列表,上述换行符是\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_value
from table
connect by level <= regexp_count(value,'[^;]+',1)
and prior class_no = class_no
and prior sys_guid() is not null
regexp_substr()函数为拆分字符串,若没有connect by语句,只是
select to_char(regexp_substr(value'[^;]+',1) as split_value
from table
则不会循环进行拆分,只会拆分第一段,比如我那个例子,只会获取到
| classno | value |
|---|---|
| 1 | ‘李世民’,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
经过上述处理之后,得到的结果应该是
| classno | value |
|---|---|
| 1 | name,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 sex
from table
- 还是用regexp_substr函数来拆分,只不过不进行递归查询,
三、代码实现
with tmp as (
select classno,replace(value,chr(10),';') as value
from table
),tmp1 as (
select to_char(regexp_substr(value,'[^;]+',1,level)) as split_value,classno
from tmp
connect by level <= regexp_count(value,'[^;]+',1)
and prior classno = classno
and 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 sex
from tmp1
)
select classno,
name,
age,
sex
from tmp2
where name != 'name'
