sql - Oracle SQL : Intersect tuple literals

Question

是否有函数或运算符或其他简单(r)构造来获取 oracle sql 中两个元组文字的交集？

考虑以下示例:

有下表

------------------------------
TABLE sometable
------------------------------
id | telephone | mobile | fax
------------------------------
 1 | 123       | 456    | 789

给定一个包含 n 个数字的列表 { n1, n2, n3, ... , n }
找到 id，这样:

   telephone = n1 or mobile = n1 or fax = n1
or telephone = n2 or mobile = n2 or fax = n2
or telephone = n3 or mobile = n3 or fax = n3
....
or telephone = n  or mobile = n  or fax = n 

两个可行的解决方案是:

1. 解决方案 1

SELECT id FROM sometable
WHERE
   n1 IN (telephone, mobile, fax)
OR n2 IN (telephone, mobile, fax)
OR n3 IN (telephone, mobile, fax)
....
OR n  IN (telephone, mobile, fax)
;

2. 解决方案 2

SELECT id FROM sometable
WHERE
   telephone IN (n1, n2, n3, ..., n)
OR mobile    IN (n1, n2, n3, ..., n)
OR fax       IN (n1, n2, n3, ..., n)
;

但是，是否有函数/运算符可以执行以下操作？

SELECT id
FROM sometable
WHERE
 intersect_function
 (
    (telephone, mobile, fax),
    (n1, n2, n3, ..., n)
 )
 = TRUE
;

考虑到此条件是更长查询的一部分，具有更多且可能更复杂的条件，因此欢迎使用替代的、更简单的构造。

谢谢。

Answer 1

我的想法是通过 with 子句将您的搜索号码转换为表格:

然后通过一个小的正则表达式技巧，您可以从一行中创建每个值一行，并将它们与您的表相匹配，这要归功于 in 子句:

create TABLE sometable
(
  id number, 
  telephone number, 
  mobile number, 
  fax number
);

insert into sometable values(1, 123, 456, 789);
insert into sometable values(2, 0, 0, 123);
insert into sometable values(3, 456, 0, 0);

with w(n) as
(
  select regexp_substr('123, 456', '\d+', 1, level) n
  from dual
  connect by regexp_instr('123, 456', '\d+', 1, level) != 0
)
select *
from sometable s, w
where w.n in (s.telephone, s.mobile, s.fax)
;

这按预期给出:

ID  TELEPHONE  MOBILE   FAX  N
1   123        456      789  123
2   0          0        123  123
1   123        456      789  456
3   456        0        0    456