查找与多个值匹配的记录组 [英] Find group of records that match multiple values

问题描述

我有以下数据:

ID --- ParentID --- DataValue  
1  ---    1     ---    1  
2  ---    1     ---    2  
3  ---    1     ---    6  
4  ---    2     ---    1  
5  ---    2     ---    2  
6  ---    2     ---    4  
7  ---    3     ---    1  
8  ---    3     ---    3  
9  ---    3     ---    5

对于每组记录(按ParentID分组)，我想找到与DataValue中所有给定值匹配的所有组，例如:

For each group of records (grouped by ParentID), I would like to find all groups that match all given values in DataValue, for example:

• 对于值(1,2)，将返回ParentID 1和2
• 对于值(1,6)，仅会返回ParentID 1(ParentID 2组不包含6)

我查看了这个问题，非常相似，但OP会查找不包含单个值的组.

I looked at this question, which is very similar but OP looks for groups that do not contain single value.

任何帮助将不胜感激！

Any help is greatly appreciated!

推荐答案

您可以通过条件聚合来做到这一点:

You can do this with conditional aggregation:

select parentid 
from tablename
group by parentid
having sum(case when datavalue = 1 then 1 else 0 end) > 0 and
       sum(case when datavalue = 6 then 1 else 0 end) > 0

另一种方法是使用exists:

select distinct parentid
from tablename t1
where exists(select * from tablename where parentid = t1.parentid and datavalue = 1) and
      exists(select * from tablename where parentid = t1.parentid and datavalue = 6)

另一种方法是计算不同的事件:

Another way is counting distinct occurrences:

select parentid 
from tablename
where datavalue in(1, 6)
group by parentid
having count(distinct datavalue) = 2

这篇关于查找与多个值匹配的记录组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！