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

Question

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

如果我去

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

，那么我会得到“红色”OR“蓝色”的对象。我需要获取所有“红色”和“蓝色”的对象。如果我去：

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

Answer 2

我更喜欢 ON 而不是 USING：

SELECT o.object_name
FROM objects o
  JOIN object_color oc
    ON o.object_id = oc.object_id
WHERE oc.object_color IN ( 'red', 'blue' )
GROUP BY o.object_id
HAVING COUNT(o.object_id) = ( SELECT COUNT(*)
                              FROM ( 'red', 'blue' )
                            )

假设一个对象不可能有许多具有相同颜色的行。

Answer 3

您需要多重加入

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

Answer 4

如果您使用的 SQL 支持，您也可以使用 INTERSECT。

SELECT object_name
FROM objects o, objects_color oc
WHERE object_color = 'red'
and o.object_id = oc.object_id

INTERSECT

SELECT object_name
FROM objects o, objects_color oc
WHERE object_color = 'blue'
and o.object_id = oc.object_id

这将使两个表相交，并且仅显示具有与相同对象名称匹配的红色和蓝色的行。