oracle sql去差集,Oracle 两个逗号分割的字符串，获取交集、差集的sql实现过程解析...

Oracle数据库的两个字段值为逗号分割的字符串，例如：字段A值为“1,2,3,5”，字段B为“2”。

想获取两个字段的交集(相同值)2，获取两个字段的差集(差异值)1,3,5。

一、最终实现的sql语句

1、获取交集(相同值)：

select regexp_substr(id, '[^,]+', 1, rownum) idfrom (select '1,2,3,5' id fromdual)

connectby rownum <= length(regexp_replace(id, '[^,]+')) +1

intersect --取交集

select regexp_substr(id, '[^,]+', 1, rownum) idfrom (select '2' id fromdual)

connectby rownum <= length(regexp_replace(id, '[^,]+')) +1;/*结果：

2*/

2、获取差集(差异值)：

select regexp_substr(id, '[^,]+', 1, rownum) idfrom (select '1,2,3,5' id fromdual)

connectby rownum <= length(regexp_replace(id, '[^,]+')) +1minus--取差集

select regexp_substr(id, '[^,]+', 1, rownum) idfrom (select '2' id fromdual)

connectby rownum <= length(regexp_replace(id, '[^,]+')) +1;/*结果：

1

3

5*/

二、实现过程用到的函数用法说明

1、regexp_substr

正则表达式分割字符串，函数格式如下：

function regexp_substr(strstr, pattern [,position] [,occurrence] [,modifier] [subexpression])

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

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

__position：可选参数，表示起始位置，从第几个字符开始正则表达式匹配(默认为1)

__occurrence：可选参数，标识第几个匹配组，默认为1

__modifier：可选参数，表示模式('i'不区分大小写进行检索；'c'区分大小写进行检索。默认为'c'。)

使用例子：

selectregexp_substr('1,2,3,5','[^,]+') ASt1,

regexp_substr('1,2,3,5','[^,]+',1,2) ASt2,

regexp_substr('1,2,3,5','[^,]+',1,3) ASt3,

regexp_substr('1,2,3,5','[^,]+',1,4) ASt4,

regexp_substr('1,2,3,5','[^,]+',2) ASt5,

regexp_substr('1,2,3,5','[^,]+',2,1) ASt6,

regexp_substr('1,2,3,5','[^,]+',2,2) ASt7fromdual;/*结果：

1 2 3 5 2 2 3*/

2、regexp_replace

通过正则表达式来进行匹配替换，函数格式如下：

function regexp_substr(srcstr, pattern [,replacestr] [,position] [,occurrence] [,modifier])

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

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

__replacestr：可选参数，替换的字符串，默认为空字符串

__position：可选参数，表示起始位置，从第几个字符开始正则表达式匹配(默认为1)

__occurrence：可选参数，标识第几个匹配组，默认为1

__modifier：可选参数，表示模式('i'不区分大小写进行检索；'c'区分大小写进行检索。默认为'c'。)

使用例子：

selectregexp_replace('1,2,3,5','5','4') t1,

regexp_replace('1,2,3,5','2|3',4) t2,

regexp_replace('1,2,3,5','[^,]+') t3,

regexp_replace('1,2,3,5','[^,]+','') t4,

regexp_replace('1,2,3,5','[^,]+','*') t5fromdual;/*结果：

1,2,3,4 1,4,4,5 ,,, ,,, *,*,*,**/

3、connect by

(1)connect by单独用，返回多行结果

select rownum from dual connect by rownum < 5;/*结果：

1

2

3

4*/

(2)一般通过start with . . . connect by . . .子句来实现SQL的层次查询

selectid,

name,

sys_connect_by_path(id,'') idpath,

sys_connect_by_path(name,'') namepathfrom(select 1 id, '广东' name, 0 pid fromdualunion

select 2 id, '广州' name , 1 pid fromdualunion

select 3 id, '深圳' name , 1 pid fromdual

)

startwith pid = 0connectby prior id =pid;/*结果：

1 广东 1 广东

2 广州 12 广东广州

3 深圳 13 广东深圳*/

三、总结

由上面函数用法，可知下面语句可以把字符串“1,2,3,5”转换为4行记录

select regexp_substr(id, '[^,]+', 1, rownum) idfrom (select '1,2,3,5' id fromdual)

connectby rownum <= length(regexp_replace(id, '[^,]+')) +1

然后在2个结果中使用集合运算符(UNION/UNION ALL 并集，INTERSECT 交集，MINUS 差集)进行最终处理。

内容来源于网络如有侵权请私信删除