查找具有所有值的ID(mysql、sql)

有很多解决方案。除了jhonny的解决方案外,还可以使用join

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

或使用Intersect

SELECT id FROM table WHERE value = 2

INTERSECT

SELECT id FROM table WHERE value = 5

尝试如下操作:

SELECT id
  FROM test
WHERE 
   value in (2,5)
GROUP by id
HAVING count(*) = 2

如果要测试它,请使用简单的表(不带索引!):

CREATE TABLE IF NOT EXISTS test (
  id int(4) NOT NULL,
  `value` int(4) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO test (id, value) VALUES
(100, 1),
(100, 3),
(101, 1),
(101, 4),
(102, 2),
(102, 5),
(103, 2),
(103, 4),
(104, 1),
(104, 3),
(105, 2),
(105, 5);

几天前我有一个非常相似的问题。看见 MySQL - Find rows matching all rows from joined table

这将是我实现这一目标的方法:

SELECT DISTINCT id
FROM table 
WHERE 
id IN (SELECT id FROM table WHERE value = 2)
AND 
id IN (SELECT id FROM table WHERE value = 5)