在子集中具有特定值的父级的 SQL 查询

2021-09-10 00:00:00 sql tsql sql-server set-theory

我试图只返回父级，其中子级集包含多个特定记录.鉴于此表:

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.

相关文章