sql - 选择至少匹配 n 个条件中的 n-1 个的所有记录

Question

我正在使用 Oracle 12c。我想知道是否可以选择匹配 n 个条件中的 n-1 个的所有记录？

例子:

CREATE TABLE users
(id number, 
firstname varchar2(100), 
lastname varchar2(100), 
city  varchar2(100));

insert into users(id, firstname, lastname, city)
 values (1, 'John', 'Smith', 'London');
insert into users(id, firstname, lastname, city)
 values (2, 'Tom',  'Smith', 'London');
insert into users(id, firstname, lastname, city)
 values (3, 'John', 'Davis', 'London');
insert into users(id, firstname, lastname, city)
 values (4, 'John', 'Smith', 'Bristol');
insert into users(id, firstname, lastname, city)
 values (5, 'Tom',  'Davis', 'London');
insert into users(id, firstname, lastname, city)
 values (6, 'Tom',  'Davis', 'Bristol');

 select * from users 
  where firstname = 'John' 
    and lastname = 'Smith'
    and city= 'London'

此选择将仅返回一条符合所有三个条件 (id = 1) 的记录。我需要的是一个查询，它返回与三个条件中至少两个匹配的所有记录(id = 1、2、3、4)。

如果我们知道用户表有 500 万条记录，在 Oracle 中是否有可能？

Answer 1

一般方法是将每个条件放在 CASE 中返回 1 或 0，并计算 1 的数量:

select * from users 
where (CASE WHEN firstname = 'John' THEN 1 ELSE 0 END
    + CASE WHEN lastname = 'Smith' THEN 1 ELSE 0 END
    + CASE WHEN city= 'London' THEN 1 ELSE 0 END) >= 2

每个匹配条件对总和贡献 1，因此您可以检查满足了多少条件。