查找与多个值匹配的记录组 [英] 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屋!
查看全文