Oracle數(shù)據(jù)庫(kù)的兩個(gè)字段值為逗號(hào)分割的字符串，例如：字段A值為“1,2,3,5”，字段B為“2”。
想獲取兩個(gè)字段的交集（相同值）2，獲取兩個(gè)字段的差集（差異值）1,3,5。
一、最終實(shí)現(xiàn)的sql語(yǔ)句
1、獲取交集（相同值）：
|
1
2
3
4
5
6
7
8
9
10
|
select regexp_substr(id, '[^,]+', 1, rownum) idfrom (select '1,2,3,5' id from dual)connect by rownum <= length(regexp_replace(id, '[^,]+')) +1intersect -- 取交集select regexp_substr(id, '[^,]+', 1, rownum) idfrom (select '2' id from dual)connect by rownum <= length(regexp_replace(id, '[^,]+')) +1;/*結(jié)果：2*/ |
2、獲取差集（差異值）：
|
1
2
3
4
5
6
7
8
9
10
11
12
|
select regexp_substr(id, '[^,]+', 1, rownum) idfrom (select '1,2,3,5' id from dual)connect by rownum <= length(regexp_replace(id, '[^,]+')) +1minus --取差集select regexp_substr(id, '[^,]+', 1, rownum) idfrom (select '2' id from dual)connect by rownum <= length(regexp_replace(id, '[^,]+')) +1;/*結(jié)果：135*/ |
二、實(shí)現(xiàn)過(guò)程用到的函數(shù)用法說(shuō)明
1、regexp_substr
正則表達(dá)式分割字符串，函數(shù)格式如下：
|
1
2
3
4
5
6
|
function regexp_substr(strstr, pattern [,position] [,occurrence] [,modifier] [subexpression])__srcstr：需要進(jìn)行正則處理的字符串__pattern：進(jìn)行匹配的正則表達(dá)式__position：可選參數(shù)，表示起始位置，從第幾個(gè)字符開始正則表達(dá)式匹配（默認(rèn)為1）__occurrence：可選參數(shù)，標(biāo)識(shí)第幾個(gè)匹配組，默認(rèn)為1__modifier：可選參數(shù)，表示模式（'i'不區(qū)分大小寫進(jìn)行檢索；'c'區(qū)分大小寫進(jìn)行檢索。默認(rèn)為'c'。） |
使用例子：
|
1
2
3
4
5
6
7
8
9
|
selectregexp_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 t7from dual; |
/*結(jié)果：
1 2 3 5 2 2 3
*/
2、regexp_replace
通過(guò)正則表達(dá)式來(lái)進(jìn)行匹配替換，函數(shù)格式如下：
|
1
2
3
4
5
6
7
|
function regexp_substr(srcstr, pattern [,replacestr] [,position] [,occurrence] [,modifier])__srcstr：需要進(jìn)行正則處理的字符串__pattern：進(jìn)行匹配的正則表達(dá)式__replacestr：可選參數(shù)，替換的字符串，默認(rèn)為空字符串__position：可選參數(shù)，表示起始位置，從第幾個(gè)字符開始正則表達(dá)式匹配（默認(rèn)為1）__occurrence：可選參數(shù)，標(biāo)識(shí)第幾個(gè)匹配組，默認(rèn)為1__modifier：可選參數(shù)，表示模式（'i'不區(qū)分大小寫進(jìn)行檢索；'c'區(qū)分大小寫進(jìn)行檢索。默認(rèn)為'c'。） |
使用例子：
|
1
2
3
4
5
6
7
|
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','[^,]+','*') t5from dual; |
/*結(jié)果：
1,2,3,4 1,4,4,5 ,,, ,,, *,*,*,*
*/
3、connect by
（1）connect by單獨(dú)用，返回多行結(jié)果
|
1
|
select rownum from dual connect by rownum < 5; |
/*結(jié)果：
1
2
3
4
*/
（2）一般通過(guò)start with . . . connect by . . .子句來(lái)實(shí)現(xiàn)SQL的層次查詢
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
selectid,name,sys_connect_by_path(id,'\') idpath,sys_connect_by_path(name, '\') namepathfrom (select 1 id, '廣東' name, 0 pid from dualunion select 2 id, '廣州' name , 1 pid from dualunion select 3 id, '深圳' name , 1 pid from dual) start with pid = 0connect by prior id = pid; |
/*結(jié)果：
1 廣東 \1 \廣東
2 廣州 \1\2 \廣東\廣州
3 深圳 \1\3 \廣東\深圳
*/
三、總結(jié)
由上面函數(shù)用法，可知下面語(yǔ)句可以把字符串“1,2,3,5”轉(zhuǎn)換為4行記錄
|
1
2
3
|
select regexp_substr(id, '[^,]+', 1, rownum) idfrom (select '1,2,3,5' id from dual)connect by rownum <= length(regexp_replace(id, '[^,]+')) +1 |
然后在2個(gè)結(jié)果中使用集合運(yùn)算符（UNION/UNION ALL 并集，INTERSECT 交集，MINUS 差集）進(jìn)行最終處理。
總結(jié)
以上所述是小編給大家介紹的Oracle 兩個(gè)逗號(hào)分割的字符串,獲取交集、差集的sql實(shí)現(xiàn)過(guò)程解析,希望對(duì)大家有所幫助，如果大家有任何疑問(wèn)請(qǐng)給我留言，小編會(huì)及時(shí)回復(fù)大家的。在此也非常感謝大家對(duì)服務(wù)器之家網(wǎng)站的支持！
如果你覺(jué)得本文對(duì)你有幫助，歡迎轉(zhuǎn)載，煩請(qǐng)注明出處，謝謝！
原文鏈接：https://www.cnblogs.com/gdjlc/archive/2019/10/23/11724187.html
