Oracle数据库的两个字段值为逗号分割的字符串，例如：字段A值为[1,2,3,5]，字段B为[2]。
想获取两个字段的交集（相同值）2，获取两个字段的差集（差异值）1,3,5。
一、最终实现的sql语句
1、获取交集（相同值）：
|
1 2 3 4 5 6 7 8 9 10 |
select regexp_substr(id, '[^,]+' , 1, rownum) id from ( select '1,2,3,5' id from dual) connect by rownum <= length(regexp_replace(id, '[^,]+' )) +1 intersect -- 取交集 select regexp_substr(id, '[^,]+' , 1, rownum) id from ( select '2' id from dual) connect by rownum <= length(regexp_replace(id, '[^,]+' )) +1; /*结果： 2 */ |
2、获取差集（差异值）：
|
1 2 3 4 5 6 7 8 9 10 11 12 |
select regexp_substr(id, '[^,]+' , 1, rownum) id from ( select '1,2,3,5' id from dual) connect by rownum <= length(regexp_replace(id, '[^,]+' )) +1 minus --取差集 select regexp_substr(id, '[^,]+' , 1, rownum) id from ( select '2' id from dual) connect by rownum <= length(regexp_replace(id, '[^,]+' )) +1; /*结果： 1 3 5 */ |
二、实现过程用到的函数用法说明
1、regexp_substr
正则表达式分割字符串，函数格式如下：
|
1 2 3 4 5 6 |
function regexp_substr(strstr, pattern [,position] [,occurrence] [,modifier] [subexpression]) __srcstr：需要进行正则处理的字符串 __pattern：进行匹配的正则表达式 __position：可选参数，表示起始位置，从第几个字符开始正则表达式匹配（默认为1） __occurrence：可选参数，标识第几个匹配组，默认为1 __modifier：可选参数，表示模式（ 'i' 不区分大小写进行检索； 'c' 区分大小写进行检索。默认为 'c' 。） |
使用例子：
|
1 2 3 4 5 6 7 8 9 |
select regexp_substr( '1,2,3,5' , '[^,]+' ) AS t1, regexp_substr( '1,2,3,5' , '[^,]+' ,1,2) AS t2, regexp_substr( '1,2,3,5' , '[^,]+' ,1,3) AS t3, regexp_substr( '1,2,3,5' , '[^,]+' ,1,4) AS t4, regexp_substr( '1,2,3,5' , '[^,]+' ,2) AS t5, regexp_substr( '1,2,3,5' , '[^,]+' ,2,1) AS t6, regexp_substr( '1,2,3,5' , '[^,]+' ,2,2) AS t7 from dual; |
/*结果：
1 2 3 5 2 2 3
*/
2、regexp_replace
通过正则表达式来进行匹配替换，函数格式如下：
|
1 2 3 4 5 6 7 |
function regexp_substr(srcstr, pattern [,replacestr] [,position] [,occurrence] [,modifier]) __srcstr：需要进行正则处理的字符串 __pattern：进行匹配的正则表达式 __replacestr：可选参数，替换的字符串，默认为空字符串 __position：可选参数，表示起始位置，从第几个字符开始正则表达式匹配（默认为1） __occurrence：可选参数，标识第几个匹配组，默认为1 __modifier：可选参数，表示模式（ 'i' 不区分大小写进行检索； 'c' 区分大小写进行检索。默认为 'c' 。） |
使用例子：
|
1 2 3 4 5 6 7 |
select regexp_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' , '[^,]+' , '*' ) t5 from dual; |
/*结果：
1,2,3,4 1,4,4,5 ,,, ,,, *,*,*,*
*/
3、connect by
（1）connect by单独用，返回多行结果
|
1 |
select rownum from dual connect by rownum < 5; |
/*结果：
1
2
3
4
*/
（2）一般通过start with . . . connect by . . .子句来实现SQL的层次查询
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
select id, name , sys_connect_by_path(id, '\') idpath, sys_connect_by_path(name, ' \ ') namepath from ( select 1 id, ' 广东 ' name, 0 pid from dual union select 2 id, ' 广州 ' name , 1 pid from dual union select 3 id, ' 深圳' name , 1 pid from dual ) start with pid = 0 connect by prior id = pid; |
/*结果：
1 广东 \1 \广东
2 广州 \1\2 \广东\广州
3 深圳 \1\3 \广东\深圳
*/
三、总结
由上面函数用法，可知下面语句可以把字符串[1,2,3,5]转换为4行记录
|
1 2 3 |
select regexp_substr(id, '[^,]+' , 1, rownum) id from ( select '1,2,3,5' id from dual) connect by rownum <= length(regexp_replace(id, '[^,]+' )) +1 |
然后在2个结果中使用集合运算符（UNION/UNION ALL 并集，INTERSECT 交集，MINUS 差集）进行最终处理。
总结
以上所述是小编给大家介绍的Oracle 两个逗号分割的字符串,获取交集、差集的sql实现过程解析,希望对大家有所帮助，如果大家有任何疑问请给我留言，小编会及时回复大家的。在此也非常感谢大家对服务器之家网站的支持！
如果你觉得本文对你有帮助，欢迎转载，烦请注明出处，谢谢！
原文链接：https://www.cnblogs.com/gdjlc/archive/2019/10/23/11724187.html
查看更多关于Oracle 两个逗号分割的字符串,获取交集、差集(sql实现过程解析)的详细内容...