找出哪个组ID包含SQL中的所有相关属性 [英] Find out what group id contains all relevant attributes in SQL
问题描述
因此,在这种情况下,我们所拥有的群体是动物群体.
So lets say in this case, the group that we have is groups of animals.
可以说我有以下表格:
animal_id | attribute_id | animal
----------------------------------
1 | 1 | dog
1 | 4 | dog
2 | 1 | cat
2 | 3 | cat
3 | 2 | fish
3 | 5 | fish
id | attribute
------------------
1 | four legs
2 | no legs
3 | feline
4 | canine
5 | aquatic
第一个表包含定义动物的属性,第二个表跟踪每个属性的含义.现在假设我们对某些数据运行查询并获得以下结果表:
Where the first table contains the attributes that define an animal, and the second table keeps track of what each attribute is. Now lets say that we run a query on some data and get the following result table:
attribute_id
------------
1
4
此数据将描述一条狗,因为它是唯一同时具有属性1和4的animal_id.我希望能够以某种方式基于第三张表获得animal_id(在这种情况下为1),本质上是已经生成的包含动物属性的表.
This data would describe a dog, since it is the only animal_id that has both attributes 1 and 4. I want to be able to somehow get the animal_id (which in this case would be 1) based on the third table, which is essentially a table that has already been generated that contains the attributes of an animal.
编辑
因此具有1和4的第三个表不必是1和4.它可以返回2和5(对于鱼),或者返回1和3(猫).我们可以假设它的结果将始终完全匹配一只动物,但是我们不知道哪一只.
So the third table that has 1 and 4 doesn't have to be 1 and 4. It could return 2 and 5 (for fish), or 1 and 3 (cat). We can assume that it's result will always match one animal completely, but we don't know which one.
推荐答案
您可以使用group by
和having
:
with a as (
select 1 as attribute_id from dual union all
select 4 as attribute_id from dual
)
select t.animal_id, t.animal
from t join
a
on t.attribute_id = a.attribute_id
group by t.animal_id, t.animal
having count(*) = (select count(*) from a);
以上内容将找到所有具有和属性的动物.如果您希望动物具有这两个属性:
The above will find all animals that have those attributes and any others. If you want animals that have exactly those 2 attributes:
with a as (
select 1 as attribute_id from dual union all
select 4 as attribute_id from dual
)
select t.animal_id, t.animal
from t left join
a
on t.attribute_id = a.attribute_id
group by t.animal_id, t.animal
having count(*) = (select count(*) from a) and
count(*) = count(a.attribute_id);
这篇关于找出哪个组ID包含SQL中的所有相关属性的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!