如何编写MySQL查询：筛选存在color元数据但无size元数据的item_id

筛选规则说明：从存储项目元数据的表中，筛选出存在meta_key为color的元数据、不存在meta_key为size的元数据的item_id，匹配示例场景下仅返回item_id=650的预期结果。

基础信息说明 #

当前涉及的表核心字段：

• meta_id：元数据主键ID
• item_id：关联项目的ID
• meta_key：元数据的键名
• meta_value：元数据的存储值

已知示例数据的匹配情况：

• item_id=980：同时存在color（值white）、size（值large）两条元数据，不符合筛选要求
• item_id=650：仅存在color（值white）一条元数据，无size相关元数据，为预期返回结果
• item_id=432：同时存在color（值white）、size（值large）两条元数据，不符合筛选要求

常用查询实现 #

写法1：NOT EXISTS 关联判断（大表场景性能更优） #

将下方语句中的your_meta_table替换为实际的元数据表名即可执行：

SELECT DISTINCT t.item_id
FROM your_meta_table t
WHERE t.meta_key = 'color'
  AND NOT EXISTS (
      SELECT 1
      FROM your_meta_table t2
      WHERE t2.item_id = t.item_id
        AND t2.meta_key = 'size'
  );

逻辑说明：先锁定所有带有color元数据的item_id，再排除掉那些关联存在size元数据的条目，完全匹配筛选规则。

写法2：LEFT JOIN 空值判断（扩展灵活） #

SELECT t1.item_id
FROM (
    SELECT DISTINCT item_id
    FROM your_meta_table
    WHERE meta_key = 'color'
) t1
LEFT JOIN your_meta_table t2
  ON t1.item_id = t2.item_id
  AND t2.meta_key = 'size'
-- 关联结果为空，说明不存在size元数据
WHERE t2.item_id IS NULL;

逻辑说明：先拿到所有带color元数据的item_id集合，再左关联同item_id下的size元数据，关联不上的条目就是符合要求的结果，后续如果需要关联其他元数据的取值，这种写法的扩展空间更大。

写法3：GROUP BY 聚合判断（多规则校验场景更直观） #

SELECT item_id
FROM your_meta_table
GROUP BY item_id
HAVING
    -- 必须存在color元数据
    SUM(CASE WHEN meta_key = 'color' THEN 1 ELSE 0 END) > 0
    -- 必须完全不存在size元数据
    SUM(CASE WHEN meta_key = 'size' THEN 1 ELSE 0 END) = 0;

逻辑说明：按item_id分组统计每个项目的元数据键分布，通过HAVING子句校验存在/不存在的规则，适合需要同时校验多组元数据条件的复杂场景。

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