在子集中具有特定值的父级的 SQL 查询
我试图只返回父级,其中子级集包含多个特定记录.鉴于此表:
I am trying to return just the parent where the set of children contain multiple specific records. Given this table:
Product State
------- -----
111 AZ
111 CA
111 UT
222 AZ
222 WA
333 CA
我想找到同时具有 AZ
子记录和 CA
子记录的 Product
列表,即使它还有其他记录.哪个会返回...
I want find the list of Product
that have both a AZ
child record and a CA
child record, even if it also has other records. Which would return ...
Product
-------
111
推荐答案
select product
from table
where state in ('az','ca')
group by product
having count(distinct(state)) = 2
select distinct product
from table
where state = 'az'
INTERSECT
select distinct product
from table
where state = 'ca'
select distinct t1.product
from table t1
join table t2
on t1.product = t2.product
and t1.state = 'az'
and t2.state = 'ca'
最后一个可能是最有效的.
The last is probably going to be the most efficient.
相关文章