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 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;/*結果：2*/
2、獲取差集（差異值）：
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;/*結果：135*/
二、實作程序用到的函式用法說明
1、regexp_substr
正則運算式分割字串，函式格式如下：
function regexp_substr(strstr, pattern [,position] [,occurrence] [,modifier] [subexpression])__srcstr：需要進行正則處理的字串__pattern：進行匹配的正則運算式__position：可選引數，表示起始位置，從第幾個字符開始正則運算式匹配（默認為1）__occurrence：可選引數，標識第幾個匹配組，默認為1__modifier：可選引數，表示模式（'i'不區分大小寫進行檢索；'c'區分大小寫進行檢索，默認為'c'，）
使用例子：
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 t7from dual; /*結果：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'，）
使用例子：
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','[^,]+','*') t5from dual; /*結果：1,2,3,4 1,4,4,5 ,,, ,,, *,*,*,**/
3、connect by
（1）connect by單獨用，回傳多行結果
select rownum from dual connect by rownum < 5;/*結果：1234*/
（2）一般通過start with . . . connect by . . .子句來實作SQL的層次查詢
select id,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;/*結果：1 廣東 \1 \廣東2 廣州 \1\2 \廣東\廣州3 深圳 \1\3 \廣東\深圳*/
三、總結
由上面函式用法，可知下面陳述句可以把字串“1,2,3,5”轉換為4行記錄
select regexp_substr(id, '[^,]+', 1, rownum) idfrom (select '1,2,3,5' id from dual)connect by rownum <= length(regexp_replace(id, '[^,]+')) +1
然后在2個結果中使用集合運算子（UNION/UNION ALL 并集，INTERSECT 交集，MINUS 差集）進行最終處理，
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/36947.html
標籤：Oracle
上一篇：oracle資料庫修改密碼有效期，解決ORA-28001: 口令已經失效
下一篇：Oracle的功能性sql