sql - 在 SQL 中使用 AND 操作的多列数据集

Question

原始表数据

select 
DTRQLFR.DETERMINATION_ID_FK, DTRQLFR.QUALIFIER_ID_FK,   DTRQLFR.QUALIFIER_OPTION_ID_FK 
from IC_DTRMN_QLFR_XREF DTRQLFR
WHERE
(DTRQLFR.QUALIFIER_ID_FK, DTRQLFR.QUALIFIER_OPTION_ID_FK) IN ((1,1), (2,3), (3,6));

查询结果

我的要求是需要像下面这样对数据集应用和操作

   ((1,1) AND (2,3) AND (3,6))  ==> need to find  DETERMINATION_ID_FK as 1
   based on (DTRQLFR.QUALIFIER_ID_FK, DTRQLFR.QUALIFIER_OPTION_ID_FK) values

预期结果

Answer 1

我认为您正试图找到包含所有给定集合的 dtrqlfr.determination_id_fk。像这样的东西可以工作:

select 
  DTRQLFR.DETERMINATION_ID_FK
from IC_DTRMN_QLFR_XREF DTRQLFR
where
  (DTRQLFR.QUALIFIER_ID_FK, DTRQLFR.QUALIFIER_OPTION_ID_FK) IN ((1,1), (2,3), (3,6))
group by DTRQLFR.DETERMINATION_ID_FK
having count(*) = 3 -- where 3 is equal to the number of sets 
;