本文介绍了在子集中具有特定值的父级的 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.
这篇关于在子集中具有特定值的父级的 SQL 查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！
本站部分内容来源互联网,如果有图片或者内容侵犯您的权益请联系我们删除！