如何使用IN运算符筛选满足全部指定子条件的spec_main_id记录？

你的原始查询仅筛选单条记录满足spec_value != ''，因此会返回10001中spec_id=11的记录（该条目的spec_value不为空）。但你的实际需求是：仅保留那些spec_main_id下，所有spec_id属于(11,12)的记录的spec_value都不为空的条目，也就是要排除存在任意一个spec_id(11/12)为空的spec_main_id。

以下是两种可行的调整方案：

方案1：子查询筛选合格的spec_main_id #

先通过分组+HAVING子句找出所有符合要求的spec_main_id（即该spec_main_id下的11、12号spec_id对应的spec_value都不为空），再基于这些spec_main_id查询具体记录：

select spec_id, spec_value, spec_main_id
from specification_master
where spec_main_id IN (
    -- 筛选出所有spec_id(11,12)对应的spec_value都不为空的spec_main_id
    select spec_main_id
    from specification_master
    where spec_id IN (11,12)
    group by spec_main_id
    -- 统计该spec_main_id下spec_value为空的11/12号记录数，要求为0
    having count(case when spec_value = '' then 1 end) = 0
)
and spec_id IN (11,12)
and spec_value != '';

方案2：使用EXISTS排除不合格的spec_main_id #

通过EXISTS子查询，直接排除那些存在spec_id(11/12)且spec_value为空的spec_main_id：

select sm1.spec_id, sm1.spec_value, sm1.spec_main_id
from specification_master sm1
where sm1.spec_main_id IN (10001,10002)
  and sm1.spec_id IN (11,12)
  and sm1.spec_value != ''
  -- 确保当前spec_main_id下没有11/12号spec_id的空值记录
  and not exists (
      select 1
      from specification_master sm2
      where sm2.spec_main_id = sm1.spec_main_id
        and sm2.spec_id IN (11,12)
        and sm2.spec_value = ''
  );

以上两种查询都会返回你期望的结果：

spec_id | spec_value | spec_main_id 
11      | xyz.com    | 10002 
12      | google.com | 10002

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