服务器之家

服务器之家 > 正文

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

时间:2020-04-16 15:57     来源/作者:gdjlc

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

相关文章

热门资讯

沙雕群名称大全2019精选 今年最火的微信群名沙雕有创意
沙雕群名称大全2019精选 今年最火的微信群名沙雕有创意 2019-07-07
玄元剑仙肉身有什么用 玄元剑仙肉身境界等级划分
玄元剑仙肉身有什么用 玄元剑仙肉身境界等级划分 2019-06-21
男生常说24816是什么意思?女生说13579是什么意思?
男生常说24816是什么意思?女生说13579是什么意思? 2019-09-17
超A是什么意思 你好a表达的是什么
超A是什么意思 你好a表达的是什么 2019-06-06
华为nova5pro和p30pro哪个好 华为nova5pro和华为p30pro对比详情
华为nova5pro和p30pro哪个好 华为nova5pro和华为p30pro对比详情 2019-06-22
返回顶部