php - SQL结果来自不同领域的研究

Question

我有这个 SQL 表:

name  | id_photo
================
word1 | 38    
word2 | 38
word7 | 39
word1 | 31
word2 | 31

我想研究所有 id_photo，例如 name = word1 AND name = word2例如，在这种情况下，我希望返回 38,31

有人可以提出解决方案吗？

Answer 1

SELECT ID_PHOTO
FROM tableNAME
WHERE name IN ('word1','word2')
GROUP BY ID_PHOTO
HAVING COUNT(*) = 2

如果未在每个 id_photo 的名称上指定唯一键，请在 COUNT 内使用 distinct

SELECT ID_PHOTO
FROM tableNAME
WHERE name IN ('word1','word2')
GROUP BY ID_PHOTO
HAVING COUNT(DISTINCT name) = 2

• SQLFiddle Demo (both queries included)