Oracle多条件匹配查询：获取满足指定列值条件的ID

表结构与测试数据 #

Oracle表auto的建表及插入数据SQL如下：

create table auto
(
  ID number,
  SEQ number,
  COLUMN_NME varchar2(10),
  RELATIONAL_OPERATOR varchar2(20),
  COLUMN_VALUE varchar2(20)
);

insert into auto SELECT 1,1,'HONDA','=','ACCORD' FROM DUAL;
insert into auto SELECT 1,2,'ENGINE','=','V6' FROM DUAL;
insert into auto SELECT 1,3,'COLOR','=','BLACK' FROM DUAL;
insert into auto SELECT 1,4,'COLOR','=','BLUE' FROM DUAL;
insert into auto SELECT 2,1,'TOYOTA','=','SIENNA' FROM DUAL;
insert into auto SELECT 2,2,'COLOR','=','BLACK' FROM DUAL;
insert into auto SELECT 2,3,'ENGINE','<>','V6' FROM DUAL;
insert into auto SELECT 3,1,'HONDA','=','ODYSSEY' FROM DUAL;
insert into auto SELECT 3,1,'COLOR','<>','BLACK' FROM DUAL;
insert into auto SELECT 3,1,'ENGINE','=','V6' FROM DUAL;
insert into auto SELECT 3,1,'HONDA','=','CRV' FROM DUAL;

需求说明 #

需要筛选出同时满足以下所有条件的ID（注意所有条件对应的RELATIONAL_OPERATOR必须为=）：

• COLUMN_NME = 'HONDA' 且 COLUMN_VALUE = 'ACCORD'
• COLUMN_NME = 'COLOR' 且 COLUMN_VALUE = 'BLACK'
• COLUMN_NME = 'ENGINE' 且 COLUMN_VALUE = 'V6'

已尝试的SQL #

用户已写出标记各条件是否满足的查询语句：

select ID,SEQ,case when column_nme = 'HONDA' and column_value = 'ACCORD' THEN 1 ELSE 0 END HONDA,
 CASE WHEN COLUMN_NME = 'ENGINE' AND COLUMN_VALUE = 'V6' THEN 1 ELSE 0 END ENGINE,
 CASE WHEN COLUMN_NME = 'COLOR' AND COLUMN_VALUE = 'BLACK' THEN 1 ELSE 0 END COLOR
 FROM AUTO;

最终解决方案 #

基于上述查询，可通过以下几种方式筛选出符合所有条件的ID：

方法1：分组聚合（最直观） #

对ID分组，统计每个ID下满足各条件的记录数，确保每个条件都至少有一条符合的记录，同时限定仅考虑RELATIONAL_OPERATOR为=的记录：

SELECT ID
FROM auto
WHERE RELATIONAL_OPERATOR = '='
GROUP BY ID
HAVING 
    SUM(CASE WHEN COLUMN_NME = 'HONDA' AND COLUMN_VALUE = 'ACCORD' THEN 1 ELSE 0 END) >= 1
    AND SUM(CASE WHEN COLUMN_NME = 'COLOR' AND COLUMN_VALUE = 'BLACK' THEN 1 ELSE 0 END) >= 1
    AND SUM(CASE WHEN COLUMN_NME = 'ENGINE' AND COLUMN_VALUE = 'V6' THEN 1 ELSE 0 END) >= 1;

方法2：子查询取交集 #

分别查询满足单个条件的ID，再取交集，得到同时满足所有条件的ID：

SELECT ID FROM auto WHERE RELATIONAL_OPERATOR = '=' AND COLUMN_NME = 'HONDA' AND COLUMN_VALUE = 'ACCORD'
INTERSECT
SELECT ID FROM auto WHERE RELATIONAL_OPERATOR = '=' AND COLUMN_NME = 'COLOR' AND COLUMN_VALUE = 'BLACK'
INTERSECT
SELECT ID FROM auto WHERE RELATIONAL_OPERATOR = '=' AND COLUMN_NME = 'ENGINE' AND COLUMN_VALUE = 'V6';

方法3：基于已有查询的子查询 #

直接在用户已有的标记查询基础上，筛选出三个标记均为1的ID（去重避免重复结果）：

SELECT DISTINCT ID
FROM (
    select ID,
           case when column_nme = 'HONDA' and column_value = 'ACCORD' THEN 1 ELSE 0 END HONDA,
           CASE WHEN COLUMN_NME = 'ENGINE' AND COLUMN_VALUE = 'V6' THEN 1 ELSE 0 END ENGINE,
           CASE WHEN COLUMN_NME = 'COLOR' AND COLUMN_VALUE = 'BLACK' THEN 1 ELSE 0 END COLOR
    FROM AUTO
    WHERE RELATIONAL_OPERATOR = '='
) t
WHERE HONDA = 1 AND ENGINE = 1 AND COLOR = 1;

以上三种方法均可得到正确结果（示例中仅ID=1符合条件）。

内容的提问来源于stack exchange，提问作者user9296849