时间戳

首页 > 解决方案 > MySQL有条件地从数组中查询多对多关系（内部连接表）

mysql - MySQL有条件地从数组中查询多对多关系（内部连接表）

问题描述

我有一张colors桌子和一张items桌子，这两张桌子之间有多对多的关系（通过一张items_colors桌子）。一个项目可以有很多颜色，一个颜色可以有很多项目。

items
   id

colors
   id
   name

items_colors
    item_id [foreign key: items(id)]
    color_id [foreign key: colors(id)]

从一组颜色中，我想获得仅匹配一种或多种提供颜色的所有项目。如果某个项目还与数组中未指定的其他颜色相关联，则不应检索它。

SELECT
    `*`
FROM
    `items`
    INNER JOIN `items_colors` ON `items_colors`.`item_id` = `items`.`id`
    INNER JOIN `colors` ON `colors`.`id` = `items_colors`.`color_id`
WHERE
    `colors`.`name` IN('green', 'blue')

在上面的示例中，我想获取与给定数组匹配的所有项目，因此所有具有green或blue或绿色和蓝色的项目。但是，如果一个项目有蓝色和红色（或只有红色，或没有颜色），它应该从结果中排除。

目前，我没有找到合适的方法来做到这一点。上面示例中的查询检索的数据比我预期的要多。谢谢你的帮助！

标签： mysqlsqljoinmany-to-many

解决方案

一种方法使用聚合：

SELECT i.*
FROM items i JOIN
     items_colors ic
     ON ic.item_id = i.id JOIn
     colors c
     ON c.id = ic.color_id
GROUP BY i.id
HAVING SUM( c.name NOT IN ('green', 'blue') ) = 0;

这不会返回颜色，但您可以使用GROUP_CONCAT(c.name).

HAVING您还可以更积极地表达该条款：

HAVING COUNT(*) = SUM( c.name IN ('green', 'blue') )

也就是说，使用它可能更有效NOT EXISTS：

select i.*
from items i
where not exists (select 1
                  from item_colors ic join
                       colors c
                       on ic.color_id = c.id
                  where ic.item_id = i.id and
                        c.name NOT IN ('green', 'blue')
                 );

推荐阅读