查找具有所有值的ID(mySQL，SQL) [英] Finding ID having all values (mySQL, SQL)

问题描述

考虑这样的表

ID     Value    
100    1    
100    3    
101    1    
101    4    
102    2    
102    5    
103    2    
103    4    
104    1    
104    3    
105    2    
105    5

问题是，如果我给定值2和5，我应该得到102和105，而102和105都同时具有值​​2和5，或者如果我给定值1和3，我应该得到100和104.

The problem is, if I give values 2 and 5 I should get 102 and 105 which both 102 and 105 are having values 2 and 5 at the same time or if I give values 1 and 3 I should get 100 and 104.

如何仅使用一个sql命令来执行此操作?我需要一个尽可能简短的查询.

How can I do this with only one sql command? I need a query as light as possible.

并使用UNION，INTERSECTION或NESTED SQL，哪一个是更快，更慢，更重的e.t.c?

and using UNION, INTERSECTION or NESTED SQLs, which one is faster, slower, heavier e.t.c?

先谢谢了 埃尔杰克

推荐答案

有很多解决方案.除了Jhonny的解决方案之外，您还可以使用联接

There's lots of solutions. Addition to the Jhonny's solution, you can use a join

SELECT DISTINCT t1.id
FROM table t1, table t2
WHERE t1.id = t2.id
  AND t1.value = 2
  AND t2.value = 5

或使用相交

SELECT id FROM table WHERE value = 2

INTERSECT

SELECT id FROM table WHERE value = 5

这篇关于查找具有所有值的ID(mySQL，SQL)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！