MySQL多条件匹配查询：需返回包含所有选定颜色的ID

我有如下数据：

预期结果：

• 选blue时，返回100和103；
• 选blue和yellow时，仅返回103；
• 选blue、yellow和white时，无结果。

核心需求：只拿包含所有选定颜色的ID。

试过用WHERE子句的IN语句，但它只会返回有任一指定颜色的ID，满足不了需求。求解决方案，接受SQL加JSON过滤这类组合方式。

给你几个可行的方案：

1. GROUP BY + HAVING（通用SQL方案） #

这是最常用的写法，几乎所有数据库都支持。比如要查同时包含blue和yellow的ID：

SELECT ID
FROM your_table
WHERE heade IN ('blue', 'yellow')
GROUP BY ID
HAVING COUNT(DISTINCT heade) = 2; -- 这里的2对应你选定的颜色数量

逻辑很直接：先筛出所有包含目标颜色的记录，按ID分组后，要求分组内不同颜色的数量等于你选的颜色总数，就能保证这个ID包含所有你要的颜色。如果是动态选颜色，在应用层根据选的数量修改HAVING里的数字就行。

2. 多EXISTS子查询（适合颜色少的场景） #

如果选的颜色不多，用多个EXISTS也很直观，比如查同时有blue和yellow的ID：

SELECT DISTINCT ID
FROM your_table t1
WHERE EXISTS (
    SELECT 1 FROM your_table t2 
    WHERE t2.ID = t1.ID AND t2.heade = 'blue'
)
AND EXISTS (
    SELECT 1 FROM your_table t3 
    WHERE t3.ID = t1.ID AND t3.heade = 'yellow'
);

每个EXISTS都确认这个ID存在对应的颜色，用AND连起来就保证所有颜色都有。

3. 结合JSON过滤（数据库支持JSON函数时用） #

如果你的数据库支持JSON操作（比如PostgreSQL、MySQL 8.0+），可以先把每个ID的颜色聚合为JSON数组，再判断数组是否包含所有目标颜色：

PostgreSQL示例： #

SELECT ID
FROM (
    SELECT ID, json_agg(heade) AS color_list
    FROM your_table
    GROUP BY ID
) AS sub
WHERE color_list @> '["blue", "yellow"]'; -- @>是JSON包含运算符

MySQL示例： #

SELECT ID
FROM (
    SELECT ID, JSON_ARRAYAGG(heade) AS color_list
    FROM your_table
    GROUP BY ID
) AS sub
WHERE JSON_CONTAINS(color_list, JSON_ARRAY('blue', 'yellow'));

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