MYSQL如何查询完全匹配所有指定color_id的关联照片数据

你之前的两种写法返回空结果的核心原因是：WHERE子句是针对单条color_photo记录做行级过滤，单条记录的color_id只能存储一个值，不可能同时等于两个不同的数值，所以c.color_id = 4 AND c.color_id = 9这类条件永远不成立，自然返回空结果。

核心思路是先筛选出所有命中目标color_id的记录，按photo_id分组后统计去重后的color_id数量，当数量和传入的color_id总数一致时，说明该photo_id匹配了所有指定颜色。

参考SQL（以传入[4,9,11]三个color_id为例） #

方案1：仅返回符合条件的照片基础信息（性能更优） #

SELECT p.*, c_matched.associated_color_ids
FROM (
    -- 先筛选出符合条件的photo_id
    SELECT 
        photo_id,
        GROUP_CONCAT(DISTINCT color_id) AS associated_color_ids -- 可选字段，返回该照片关联的所有匹配颜色
    FROM color_photo
    WHERE color_id IN (4,9,11)
    GROUP BY photo_id
    -- 去重后命中的颜色数量等于传入的颜色总数，说明全部匹配
    HAVING COUNT(DISTINCT color_id) = 3
) AS c_matched
LEFT JOIN photos p ON c_matched.photo_id = p.id;

方案2：返回符合条件的照片对应的所有color_photo关联记录 #

如果需要保留color_photo表的全量关联字段，可以使用子查询过滤符合条件的photo_id后再关联：

SELECT c.*, p.*
FROM color_photo c
LEFT JOIN photos p ON c.photo_id = p.id
WHERE c.photo_id IN (
    SELECT photo_id
    FROM color_photo
    WHERE color_id IN (4,9,11)
    GROUP BY photo_id
    HAVING COUNT(DISTINCT color_id) = 3
)

如果传入的color_id列表是动态生成的，只需要同步修改两处即可：

• 修改WHERE color_id IN (...)里的目标颜色列表
• 修改HAVING COUNT(DISTINCT color_id) = N中的N为当前传入的color_id总个数

优化提示：如果你的业务逻辑已经保证color_photo表中同一个photo_id不会重复关联同一个color_id，可以去掉语句中的DISTINCT关键字，进一步提升查询性能。

内容的提问来源于stack exchange，提问作者twan