oracle的LISTAGG函数 和 REGEXP_SUBSTR函数的使用

oracle 聚合函数 LISTAGG ，将多行结果合并成一行

LISTAGG(列名,’ 分割符号‘)

oracle 11g 以上的版本才有的一个将指定列名的多行查询结果，用 指定的分割符号 合并成一行显示：

例如：

表原始数据：

需求：将 mb1_Transport_License_list 表中的数据，根据 transportation_license_id 数据进行分组，对 Item_Category_Name 列的数据进行 去重合并

使用聚合函数 LISTAGG 解决

SELECT transportation_license_id,LISTAGG( to_char(Item_Category_Name),',') WITHIN GROUP(ORDER BY Item_Category_Name) AS employees
      FROM ( select distinct transportation_license_id,item_category_name from mb1_Transport_License_list  ) group by transportation_license_id

SQL解析：

select distinct transportation_license_id,item_category_name from mb1_Transport_Lincense_list ; – 对需要做合并处理的数据源数据进行去重处理，如果实际要求不需要去重处理，这里可以直接改为 表名，（例如： from mb1_Transport_Lincense_list）进行查询

LISTAGG( to_char(Item_Category_Name),‘,’) WITHIN GROUP(ORDER BY Item_Category_Name) – 将 Item_Category_Name 列的内容以”, “进行分割合并、排序；

to_char(Item_Category_Name) – to_char(列名) 解决使用聚合函数 LISTAGG 进行查询后，对查询结果乱码问题进行转码处理；

运行后的结果：

oracle REGEXP_SUBSTR函数

REGEXP_SUBSTR函数格式如下：

function REGEXP_SUBSTR(String,pattern,position,occurrence,modifier)

__String ：需要进行正则处理的字符串

__pattern ：进行匹配的正则表达式

__position ：起始位置，从第几个字符开始正则表达式匹配（默认为1）

__occurrence ：标识第几个匹配组，默认为1

__modifier ：模式（’i’不区分大小写进行检索；’c’区分大小写进行检索。默认为’c’。）

1、查询使用正则分割后的第一个值，也就是17

[sql]

SELECT REGEXP_SUBSTR(‘17,20,23’,’[^,]+’,1,’i’) AS STR FROM DUAL;

结果: 17

2、查询使用正则分割后的最后一个值，也就是23

[sql]

SELECT REGEXP_SUBSTR(‘17,3,’i’) AS STR FROM DUAL;

结果: 23

3、获取一个多个数值的列，从而能够让结果以多行的形式展示出来

[sql]

SELECT LEVEL FROM DUAL CONNECT BY LEVEL <=7;

结果:

–1

–2

–3

–4

–5

–6

–7

4、将上面REGEXP_SUBSTR的occurrence关联

[sql]

SELECT NVL(REGEXP_SUBSTR(‘17,‘[^,LEVEL,‘i’),‘NULLL’) AS STR

FROM DUAL CONNECT BY LEVEL <= 7;

结果：

– 17

–20

–23

–NULL

–NULL

–NULL

–NULL

5、优化上面的SQL语句，让生成的行的数量符合实际情况

[sql]

SELECT REGEXP_SUBSTR(‘17,‘i’) AS STR

FROM DUAL CONNECT BY LEVEL <=

LENGTH(‘17,23’) - LENGTH(REGEXP_REPLACE(‘17,’,”))+1;

结果:

– 17

–20

–23

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!