一行数据转换成多行数据

时间:2021-10-18 15:04:21


 SELECT REGEXP_SUBSTR('1,2,3,4,5,6,7,8,9', '[^,]+', 1, ROWNUM)
  FROM DUAL
CONNECT BY ROWNUM <= LENGTH('1,2,3,4,5,6,7,8,9') -
           LENGTH(REPLACE('1,2,3,4,5,6,7,8,9', ',', '')) + 1


一行数据转换成多行数据


分析讲解:

oracle中REGEXP_SUBSTR方法的使用

注:本内容来源于《oracle中REGEXP_SUBSTR方法的使用

有一张tt_ticket表,用来存机票信息。里边有一个字段叫schedule,表示的是行程,存储格式为:北京/虹桥 
由于公司位于上海。而上海眼下有两个机场:浦东和虹桥。所以对于出发地或到达地为上海的机票来说,行程中会存虹桥或浦东,当然。有时候可能也会直接存上海(可能性非常小。但不代表没有)。这样,行程对于出发地为上海的行程来说。可能有下面几种可能: 
a.虹桥/北京 
b.浦东/北京 
c.上海/北京 
如今要实现搜索出发城市为上海时,把这三条信息所有拉出来。 
首先,创建一张城市地区映射表tt_ticket_city_mapper,包括字段city和mapper_city,存储下面数据:

city mapper_city
虹桥 上海
浦东 上海
上海 上海

下面先把sql贴出来,是mybatis中的部分代码:

      select * 
      from tt_ticket 
      where 1=1               
<if test="departureCity !='' and departureCity !=null">
<![CDATA[  
and (nvl(
         (select   m.mapper_city 
          from    tt_ticket_city_mapper m 
          where   m.city  = trim((select                   REGEXP_SUBSTR(t.schedule,'[^/]+',1,1)
           from dual))) ,
     trim((select REGEXP_SUBSTR(t.schedule,'[^/]+',1,1) from dual))
    ) 
    = 
   nvl(
       (select m.mapper_city 
        from   tt_ticket_city_mapper m 
        where  m.city = trim(#{departureCity}) 
      ),
         #{departureCity}
       )
      )
]]>
</if>
<if test="arriveCity !='' and arriveCity !=null"> 
<![CDATA[  
and(nvl(
       (select   m.mapper_city 
        from    tt_ticket_city_mapper m 
         where   m.city  = trim((select REGEXP_SUBSTR(t.schedule,'[^/]+',1,2) from dual))
        ) ,
        trim((select REGEXP_SUBSTR(t.schedule,'[^/]+',1,2) from dual))
   ) 
     = 
   nvl(
       (select m.mapper_city 
        from   tt_ticket_city_mapper m 
        where  m.city = trim(#{arriveCity}) 
                 ),
         #{arriveCity}
        )
]]>
</if>

先解释一下:select REGEXP_SUBSTR(t.schedule,’[^/]+’,1,1) from dual 
比方: 
select REGEXP_SUBSTR(‘虹桥/北京’,’[^/]+’,1,1) from dual 
返回的是虹桥 
select REGEXP_SUBSTR(‘虹桥/北京’,’[^/]+’,1,2) from dual 
返回的是北京 
第一个參数是要解析的字符串,第二个是正在表达式,第三个表示字符串解析时的起始位置。比方: 
select REGEXP_SUBSTR(‘虹桥/北京’,’[^/]+’,2,1) from dual 
返回的是桥 
第四个參数表示取拆分后数组里的第几个值。 
这种话 
nvl( 
(select m.mapper_city 
from tt_ticket_city_mapper m 
where m.city = trim((select REGEXP_SUBSTR(t.schedule,’[^/]+’,1,1) from dual)) 
) , 
trim((select REGEXP_SUBSTR(t.schedule,’[^/]+’,1,1) from dual)) 
) 
就能够取出/前面的字符串,比方浦东或虹桥,然后在 tt_ticket_city_mapper中查询得到器映射的城市上海。假设没有的话就是它自己 
等于号后边相同是做一下转换。


当然,看到这,肯定会有人说为什么不直接存出发城市和到达城市两个字段?那是由于行程可能是 北京/香港/迈阿密/香港/北京 这种数据。



Oracle中REGEXP_SUBSTR及其它支持正则表达式的内置函数小结

注:本内容来源于《Oracle中REGEXP_SUBSTR及其它支持正则表达式的内置函数小结


Oracle中REGEXP_SUBSTR函数的使用说明:

题目如下:
在oracle中,使用一条语句实现将'17,20,23'拆分成'17','20','23'的集合。

REGEXP_SUBSTR函数格式如下:
function REGEXP_SUBSTR(String, pattern, position, occurrence, modifier)
__srcstr :需要进行正则处理的字符串
__pattern :进行匹配的正则表达式,
匹配的值将返回,返回策略由__occurrence决定
__position :起始位置,从第几个字符开始正则表达式匹配(默认为1)
__occurrence :标识第几个匹配组,默认为1
__modifier :模式('i'不区分大小写进行检索,'c'区分大小写进行检索。默认为'c'。)

1、查询使用正则分割后的第一个值,也就是17
[sql] 
SELECT REGEXP_SUBSTR('17,20,23','[^,]+',1,1,'i') AS STR FROM DUAL; 
结果: 
STR
-----
17

2、查询使用正则分割后的最后一个值,也就是23
[sql] 
SELECT REGEXP_SUBSTR('17,20,23','[^,]+',1,3,'i') AS STR FROM DUAL; 
结果:
STR
----
23

3、获取一个多个数值的列,从而能够让结果以多行的形式展示出来
[sql] 
SELECT LEVEL FROM DUAL CONNECT BY LEVEL <=7; 
结果: 
LEVEL
----
1
2
3
4
5
6
7

4、将上面REGEXP_SUBSTR的occurrence关联
[sql] 
SELECT NVL(REGEXP_SUBSTR('17,20,23', '[^,]+', 1, LEVEL, 'i'), 'NULL') AS STR 
FROM DUAL 
CONNECT BY LEVEL <= 7; 
结果: 
STR 
----
17
20
23
NULL
NULL
NULL
NULL

5、优化上面的SQL语句,让生成的行的数量符合实际情况
[sql] 
SELECT REGEXP_SUBSTR('17,20,23', '[^,]+', 1, LEVEL, 'i') AS STR 
FROM DUAL 
CONNECT BY LEVEL <= 
LENGTH('17,20,23') - LENGTH(REGEXP_REPLACE('17,20,23', ',', ''))+1; 
结果: 
STR
----
17
20
23

 

 

Oracle正则表达式(regularexpression)

元字符

字符含义

举例说明

^

匹配字符串的开始位置(在[]中使用,此时它表示不接受该字符集合。

^a:匹配以a开始的字符串

[^a]:匹配不含a的字符串

-

当使用在a-m表示范围;

当使用在第一个字符时表示

连字符串,如[-abc]

 

$

匹配字符结束位置

‘a$':匹配以a结束的字符串

.

匹配除换行符 n之外的任何单字符。

 

?

匹配前面的子表达式零次或一次

tr(y(ing)?):可以表示try或者trying

*

匹配前面的子表达式零次或多次

 

+

匹配前面的子表达式一次或多次

 

()

标记一个子表达式的开始和结束位置

a(b)*可以匹配

ab,abb,abbb,youabb

(c|d)匹配c或d

[]

标记一个中括号表达式

[cd]匹配选择c或d等同于

(c|d)。匹配的是单字符,[^cd]是匹配c,d之外的单字符。

[a-z]表示所有小写字母

{m,n}

m= <出现次数 <=n,'{m}'表示出现m次,'{m,}'表示至少出现m次。

 

|

表示或者的关系。指明两项之间的一个选择

 

字符簇

字符含义

 

[[:alpha:]]

任何字母。

 

[[:digit:]]

[[:digit:]] 任何数字。

 

[[:alnum:]]

任何字母和数

 

[[:space:]]

任何白字符。

 

[[:upper:]]

任何大写字母。

 

[[:lower:]]

任何小写字母。

 

[[:punct:]]

任何标点符号。

 

[[:xdigit:]]

任何16进制的数字,相当于[0-9a-fA-F]。

 

Oracle支持正则表达式的内置函数

名称

语法

备注

REGEXP_LIKE

REGEXP_LIKE

(source_string,

pattern

[, match_parameter]

)

source_string:源字符串

Pattern:正则表达式

match_parameter:匹配模式(i:不区分大小写;c:区分大小写;n:允许使用可以匹配任意字符串的操作符;m:将x作为一个包含多行的字符串。

REGEXP_REPLACE

REGEXP_REPLACE

(source_string,

pattern

[,replace_string]

[,position]

[,occurtence]

[,match_parameter]

)

 

replace_string:用于替换的字符串

Position:开始搜索的起始位置

occurtence指定替换第n次出现字符串

其他同上。

REGEXP_SUBSTR

REGEXP_SUBSTR

(source_string, pattern

[,position

[,occurrence

[,match_parameter]]]

)

Position:指定在字符串中准确位置,即开始匹配的字符的位置,默认为1

Occurrence:指定在源字符串匹配过程中相对其他字符串,哪个字符串应该匹配。例如

select regexp_substr('The zip code80831 is for falcon, co',

'[[:alpha:]]{3,}',

1,

3)

from dual;

结果选择的是code而非The或zip。

select regexp_substr('The zip code80831 is for falcon, co',

'[[:alpha:]]{3,}',


11,//表示从第11个字符(code的d字符)开始

1)//查找到第一个匹配的就是for了,第二个匹配的是falcon

from dual;

结果是for

REGEXP_INSTR

REGEXP_INSTR

(source_string,

pattern

[,start_position

[,occurrence

[,return_option

[,match_parameter]]]]

)

start_position:

开始搜索位置

Occurrence:

第n次出现pattern,默认为1

return_option:

0:pattern的起始位置

1:pattern下一个字符起始位置

默认为0

REGEXP_COUNT

REGEXP_COUNT

(source_string,

pattern

[[,start_position]

[,match_parameter]])

11g新增的函数,表示pattern在原字符串中出现的次数

start_position:

开始搜索的位置

 

Oracle支持正则表达式函数举例。

Regexp_substr:用来提取一个字符串的一部分。

Regexp_substr('first filed, second filed, thirdfiled', [^,]*,')

Pattern为', [^,]*,'表示以,开始逗号,中间空一个格,连续0或多个非逗号字符,最后还是逗号,这样,返回则为', second filed,'。

-------------------------------------------------------------------------------

Regexp_instr:用来pattern的起始位置。

如有一组地址,返回邮编的位置

regexp_instr('Jone Smith,10045 Berry Lane,SanJoseph,CA

91234-1234','[[:digit:]]{5}([-[[:digit:]]{4}])?$')

显然结果位置是91234-1234对应的起始位置。

-------------------------------------------------------------------------------

Regexp_replace:用来将pattern替换成需要替换的字符串,相比传统的replace函数,该函数相比较灵活。例如:

'Jone   smith'中有三个空格,而'Jone  smith'有2个空格,要将中间的空格改为一个空格,使用replace就要使用2次,而是用正则表达式,我们就就可以将pattern设计为

'( ){2,}'这样

regexp_replace(mc,'( ){2,}',' ')

-------------------------------------------------------------------------------

regexp_like是增强版的like,可以包含_和%通配符,使用在where条件中。

正则表达式特性---后向引用

通过将自表达式匹配的内容缓存到缓冲区中,从左相右进行编号,以/digit(digit为1-9)。而子表达式用圆括号表示。

1.在regexp_replace中应用:

将字符串'aa bb cc'反写成'cc bb aa'则可以设计如下:

regexp_replace('aa bbcc','(.*) (.*) (.*)','\3 \2 \1')

(.*)则表示任何一个字符串组合,三个这样的组合用空格隔开,与源字符串吻合,\1,\2,\3则分别存储了三个字表达式的值,这样我们就可以使用这个达到目的。

2.在regexp_substr中应用:

查找被空格隔开的重复字母数字值

regexp_substr('the finaltest is is the implention',([[:alnum:]]+)([[:space:]]+)\1')

返回结果为is is 。([[:alnum:]]+)([[:space:]]+)返回很多字符串,但是增加一个\1就表示连续返回两个同样字符串,这样就把重复的串给找出来。





oracle进阶之connect by笔记

注:本文来源于《oracle进阶之connect by笔记


oracle进阶之connect by笔记

 

  connect by 用于存在父子,祖孙,上下级等层级关系的数据表进行层级查询。

  语法格式:
    { CONNECT BY [ NOCYCLE ] condition [AND condition]... [ START WITH condition ]
    | START WITH condition CONNECT BY [ NOCYCLE ] condition [AND condition]...
    }

  特殊词讲解

    start with: 指定起始节点的条件

    connect by: 指定父子行的条件关系

    prior: 查询父行的限定符,格式: prior column1 = column2 or column1 = prior column2 and ... ,

    nocycle: 若数据表中存在循环行,那么不添加此关键字会报错,添加关键字后,便不会报错,但循环的两行只会显示其中的第一条

    循环行: 该行只有一个子行,而且子行又是该行的祖先行

    connect_by_iscycle: 前置条件:在使用了nocycle之后才能使用此关键字,用于表示是否是循环行,0表示否,1 表示是

    connect_by_isleaf: 是否是叶子节点,0表示否,1 表示是

    level: level伪列,表示层级,值越小层级越高,level=1为层级最高节点

  自定义数据:

一行数据转换成多行数据
-- 创建表
create table employee(
       emp_id number(18),
       lead_id number(18),
       emp_name varchar2(200),
       salary number(10,2),
       dept_no varchar2(8)
);

-- 添加数据
insert into employee values('1',0,'king','1000000.00','001');
insert into employee values('2',1,'jack','50500.00','002');
insert into employee values('3',1,'arise','60000.00','003');
insert into employee values('4',2,'scott','30000.00','002');
insert into employee values('5',2,'tiger','25000.00','002');
insert into employee values('6',3,'wudde','23000.00','003');
insert into employee values('7',3,'joker','21000.00','003');commit;
一行数据转换成多行数据


  数据列表展示:

  一行数据转换成多行数据

  数据树形展示:

  一行数据转换成多行数据

  (1) 查询以emp_id为0开始的节点的所有直属节点

一行数据转换成多行数据
     select emp_id,lead_id,emp_name,prior emp_name as lead_name,salary
     from employee
     start with  lead_id=0
     connect by prior emp_id =  lead_id 

  -- 等同于

   select emp_id,lead_id,emp_name,prior emp_name as lead_name,salary
     from employee
     start with  emp_id=1
     connect by prior emp_id =  lead_id
一行数据转换成多行数据

  一行数据转换成多行数据

  (2) 以emp_id为6的所有祖先节点

       select emp_id,lead_id,emp_name,salary
       from employee 
       start with emp_id=6
       connect by prior lead_id=emp_id;

一行数据转换成多行数据

  (3) 查询一个节点的叔叔伯父节点

一行数据转换成多行数据
       with t as (
       select  employee.*,prior emp_name,level le
       from employee 
       start with lead_id = 0
       connect by lead_id=prior emp_id
       )
       select *
       from t 
       left join t tt on tt.emp_id=6
       where t.le = (tt.le-1)
       and t.emp_id not in (tt.lead_id)    
一行数据转换成多行数据

一行数据转换成多行数据

  (4) 查询族兄

一行数据转换成多行数据
       with t as (
            select employee.*,prior emp_name,level le
           from employee 
           start with lead_id=0
           connect by lead_id= prior emp_id
       )
       select t.*
       from t  t
       left join t tt on tt.emp_id=6
       where t.le=tt.le and t.emp_id<>6;
一行数据转换成多行数据

一行数据转换成多行数据

  (5) level伪列的使用,格式化层级

       select lpad(' ',level*2,' ')||emp_name as name,emp_id,lead_id,salary,level
       from employee
       start with lead_id=0
       connect by prior emp_id=lead_id

一行数据转换成多行数据

  level数值越低级别越高


  (6) connect_by_root 查找根节点

       select connect_by_root emp_name,emp_name,lead_id,salary
       from employee  
       where dept_no='002'
       start with lead_id=1
       connect by  prior emp_id = lead_id;

一行数据转换成多行数据

  (7) 标注循环行

一行数据转换成多行数据
       -- 插入一条数据,与另一条emp_id=7的数据组成循环行
       insert into employee values('3',7,'joker_cycle','21000.00','003');
       commit;
       -- connect_by_iscycle("CYCLE"), connect by nocycle
       select emp_id,emp_name,lead_id,salary,connect_by_iscycle as cycle 
       from employee 
       start with lead_id=0
       connect by nocycle prior emp_id = lead_id;
一行数据转换成多行数据

一行数据转换成多行数据


  (8) connect_by_isleaf 是否是叶子节点

       select emp_id,emp_name,lead_id,salary,connect_by_isleaf
       from employee
       start with lead_id=0
       connect by nocycle prior emp_id=lead_id;

一行数据转换成多行数据




« 
»