如何在组成表上进行交集 [英] How to do intersection on a composition table

问题描述

我有一个具有多对多关系的简单SQL关系模型.这是组成表

I have a simple SQL relational model with a many to many relation. Here is the composition table

 ___________________________
| object1_id  | object2_id  |
|---------------------------|

我想知道一组object2共有的所有object1.我的基本感觉是发出这样的请求

I would like to know all the object1 that are common to a set of object2. My basic feeling is to do a request like this

SELECT c.object1_id FROM composition c WHERE c.object2_id = <given_id_1>
INTERSECT 
SELECT c.object1_id FROM composition c WHERE c.object2_id = <given_id_2>

如果我在集合中有N个object2，我将做N个INTERSECT

And if I have N object2 in the set, I'll do N INTERSECT

SELECT c.object1_id FROM composition c WHERE c.object2_id = <given_id_1>
INTERSECT 
SELECT c.object1_id FROM composition c WHERE c.object2_id = <given_id_2>
...
INTERSECT 
SELECT c.object1_id FROM composition c WHERE c.object2_id = <given_id_N>

但是，它看起来并没有非常优化.你能帮助我吗 ?我不是SQL专家.我想我可以使用JOIN来做到这一点.

However, it doesn't look very optimized. Can you help me ? I am not really a SQL expert. I think I could use a JOIN to do that.

样品

 ___________________________
| object1_id  | object2_id  |
|---------------------------|
|         10  |           1 |
|         11  |           1 |
|         10  |           2 |
|         12  |           2 |
|         10  |           3 |
|         11  |           3 |
|         13  |           3 |

示例

• {object2_id set} => {预期object1_id}
• {1，2} => {10}
• {1，3} => {10，11}
• {1，2，3} => {10}

• { object2_id set } => { expected object1_id }
• { 1, 2 } => { 10 }
• { 1, 3 } => { 10, 11 }
• { 1, 2, 3 } => { 10 }

推荐答案

在性能方面，您的查询看起来还可以.您是否进行过测量以查看是否确实存在问题?

In terms of performance your query looks OK. Have you measured it to see if there really is a problem?

如果(object1_id, object2_id)是唯一的，则可以更简洁地编写查询，如下所示:

If (object1_id, object2_id) is unique then you can write the query more concisely as follows:

SELECT object1_id
FROM composition
WHERE object2_id IN (id1, id2, ..., id6)
GROUP BY object1_id
HAVING COUNT(*) = 6

请注意，6是提供的ID的数量.如果提供了不同数量的ID，则应对此进行更改.您必须测量您的dadta上的实际性能，以查看这是否可以提高速度.

Note that the 6 is the number of provided IDs. This should be changed if a different number of IDs is provided. You would have to measure the actual performance on your dadta to see if this gives any speed increase.

如果您不能假设唯一性，那么这应该起作用:

If you can't assume uniqueness then this should work:

SELECT object1_id
FROM composition
WHERE object2_id IN (id1, id2, ..., id6)
GROUP BY object1_id
HAVING COUNT(DISTINCT object2_id) = 6

最重要的是确保您的桌子上有适当的索引！这比您编写一个查询还是另一个查询更重要.

The most important thing though is to make sure you have appropriate indexes on your table! This is far more important than whether you write one query or the other.

这篇关于如何在组成表上进行交集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！