SQL查询如何筛选同时满足多个指定字段值的对应记录

核心需求实现逻辑 #

你需要筛选同一个分组值（c列/Pet Shop列）下同时存在两种指定枚举值的记录，IN运算符本身是「或匹配」逻辑，只需在此基础上增加分组计数判断即可实现需求。

简化宠物店场景解决方案 #

方案1：窗口函数实现（语法简洁，适配主流数据库）

select * from (
    select *,
           count(distinct Animal) over (partition by `Pet Shop`) as animal_type_cnt
    from table t
    where Animal in ('Cat', 'Dog')
) t
where animal_type_cnt = 2
order by `Pet Shop` asc

逻辑说明：先过滤仅保留猫狗相关记录，再按宠物店分组统计不同动物种类数，筛选出种类数为2的分组，就是同时售卖猫狗的宠物店记录。

方案2：子查询实现（兼容性更强，适配低版本数据库）

select * from table t
where `Pet Shop` in (
    -- 先查找到同时售卖猫狗的宠物店列表
    select `Pet Shop`
    from table
    where Animal in ('Cat', 'Dog')
    group by `Pet Shop`
    having count(distinct Animal) = 2
)
and Animal in ('Cat', 'Dog')
order by `Pet Shop` asc

原业务SQL改造方案 #

使用窗口函数改造后的语句如下：

select a, b, c, d, e, f, g, h, i
from (
    select t.a, t.b, t.c, t.d, t.e, t3.f, t.g, t.h, t.i,
           count(distinct h) over (partition by c) as h_type_cnt
    from table t
    inner join table2 t2 on t.c = t2.c
    inner join table3 t3 on t3.f = t2.f
    inner join table4 t4 on t4.b = t.b
    where e = e1
      and h in (h1, h2)
      and c not in (select c from table t where (h = h1 and i = i0))
) t
where h_type_cnt = 2
order by h asc

如果你的数据中同一个c对应的h值无重复，可以把count(distinct h)替换为count(h)，查询性能会更好。

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