问题描述
我试图只返回父级,其中子级集包含多个特定记录.鉴于此表:
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.