SQL:列a的实例，列b等于值1和2

Question

基本上，我希望结果显示列c的任何实例，其中列h等于'h1'和h2。

我尝试使用IN操作符，但它显示列c的任何实例，其中列h等于'h1'或"氢气">

这是我目前为止写的:

select a, b, c, d, e, f, g, h, i
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
-- note: a,c,h are the columns in question, everything else is extra info
where e = e1
and h in (h1, h2)
and c not in (select c from table t where (h = h1 and i = i0))
order by h asc

和结果(只有相关列)返回为:

a   c   h
----------
a1  c1  h1
a1  c1  h1
a2  c2  h2
a2  c2  h2
a3  c3  h1
a3  c3  h2

但是我想让它只得到这个:

a   c   h
----------
a3  c3  h1
a3  c3  h2

编辑:我将简化这一点，并使用一个例子，希望这更有意义

基本上，我希望结果显示列Pet Shop的任何实例，其中列Animal等于'Cat'和‘狗’。

我尝试使用IN运算符，但它显示列Pet Shop的任何实例，其中列Animal等于'Cat'或"狗">

这是我目前为止写的:

select * from table t
where Animal in (Cat, Dog)
order by Pet Shop asc

，结果返回为:

Pet Shop     Animal
-------------------
Dogs Galore  Dog 1
Dogs Galore  Dog 2
Cats Galore  Cat 1
Cats Galore  Cat 2
Pet 'r' us   Dog 3
Pet 'r' us   Cat 3

但是我想让它只得到这个:

Pet Shop     Animal
-------------------
Pet 'r' us   Dog 3
Pet 'r' us   Cat 3

因为它是唯一一家同时拥有和的宠物店猫和狗

Answer 1

您有一个结果集，但是您只想显示其中的行，其中对于a和c对存在h = 'h1'和h = 'h2'的行。一种方法是在分析函数中使用条件聚合:

select a, b, c, d, e, f, g, h, i
from
(
select
a, b, c, d, e, f, g, h, i,
case when
count(case when h = 'h1' then 1 end) over (partition by a, c) > 0 and
count(case when h = 'h2' then 1 end) over (partition by a, c) > 0
then 'yes' else 'no' end as both_exist
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'))
) checked
where both_exist = 'yes'
order by a, c, h;

(免责声明:我没有真正检查你的查询，但它是。我只是添加了both_exists表达式，这应该是您所需要的。)

Answer 2

首先，我们将使用cte删除重复项。删除重复项后，我们将与Count的h值大于2的表合并。

;WITH CTE AS (
SELECT DISTINCT * FROM sample)
SELECT S.*
from CTE S
INNER JOIN (SELECT a,c FROM CTE WHERE h in ('h1','h2') group by a,c having count(*)>=2)T1 ON T1.a= S.a AND T1.c =S.C;