php - 选择在规范化表中具有多个匹配项的项目

Question

我有一个存储一堆对象的表。每个对象可以有多种颜色，这些颜色存储在由 object_id 连接的规范化表中。

如果我去

SELECT `object_name` FROM `objects`
  LEFT JOIN `object_color` USING `object_id`
    WHERE `object_color` IN ('red', 'blue');

然后我得到“红色”或“蓝色”的对象。我需要获取所有“红色”和“蓝色”的对象。如果我去:

SELECT `object_name` FROM `objects`
  LEFT JOIN `object_color` USING `object_id`
    WHERE `object_color` = 'red' AND `object_color` = 'blue';

然后我什么也得不到，因为每一行中只有一个 object_color 并且不能两者都是。此外，实际上，颜色是在另一个表中带有名称的 id。为了这个问题，我在这里简化了一切。

我需要能够搜索无限数量的颜色。

谢谢

编辑:

object_color 仅在 object_color 表中。

并且任何对象都将具有任何一种颜色。

Answer 1

Select object_name
From objects
Where object_color In('red','blue')
Group By object_name
Having Count(Distinct object_color) = 2

顺便说一句，你从来没有提到 object_color 来自哪个表专栏推导。如果是来自 object_color表:

Select O.object_name
From objects As O
    Join object_color As C
        On C.object_id = O.object_id
Where C.object_color In('red','blue')
Group By O.object_name
Having Count(Distinct C.object_color) = 2

---

以上查询假定给定的 object行可能没有多个 object_color相同颜色的行。但是，正如 Joel C 指出的那样，如果有可能 object有多个 object_color红色或蓝色行，则需要不同的查询。 :

Select ...
From objects As O
Where O.object_id In    (
                        Select C1.object_id
                        From object_color As C1
                        Where C1.object_color = 'red'
                        )
    And O.object_id In  (
                        Select C1.object_id
                        From object_color As C1
                        Where C1.object_color = 'blue'
                        )

另一种解决方案:

Select O.object_name
From objects As O
    Join    (
            Select C1.object_id, C1.object_color
            From object_color As C1
            Where C1.color In('red','blue')
            Group By C1.object_id, C1.object_color
            ) As Z
        On Z.object_id = O.object_id
Group By O.object_name
Having Count(*) = 2