MySQL中实体表与属性表关联，如何编写优雅的多属性匹配SQL查询

嘿，这个场景我经常碰到，嵌套子查询确实不够优雅，给你几个更简洁高效的写法，按需选用：

方法1：分组聚合筛选（最通用，扩展性强） #

这种方法通过过滤符合条件的记录，再按实体分组统计满足条件的数量，只有当数量等于要求的条件数时，才说明该实体同时满足所有条件。

SELECT e.ID, e.NAME
FROM entity e
JOIN entity_props ep ON e.ID = ep.ENTITY_ID
WHERE (ep.PROP_ID = 23 AND ep.PROP_VALUE = 'abc')
   OR (ep.PROP_ID = 24 AND ep.PROP_VALUE = 'def')
   OR (ep.PROP_ID = 25 AND ep.PROP_VALUE = 'xyz')
GROUP BY e.ID, e.NAME
HAVING COUNT(DISTINCT ep.PROP_ID) = 3;

说明：如果每个ENTITY_ID + PROP_ID是唯一的（不会有重复的属性ID对应同一个实体），可以去掉DISTINCT，直接用COUNT(*) = 3，效率更高。

方法2：多表自连接（逻辑直观，条件少的时候好用） #

把entity_props表分别连接三次，每次对应一个属性条件，这样能直接筛选出同时满足所有条件的实体。

SELECT e.ID, e.NAME
FROM entity e
JOIN entity_props ep1 ON e.ID = ep1.ENTITY_ID
JOIN entity_props ep2 ON e.ID = ep2.ENTITY_ID
JOIN entity_props ep3 ON e.ID = ep3.ENTITY_ID
WHERE ep1.PROP_ID = 23 AND ep1.PROP_VALUE = 'abc'
  AND ep2.PROP_ID = 24 AND ep2.PROP_VALUE = 'def'
  AND ep3.PROP_ID = 25 AND ep3.PROP_VALUE = 'xyz';

说明：这种写法的好处是逻辑非常直白，一眼就能看清楚每个条件，但如果需要添加更多属性条件，就得增加更多的JOIN，扩展性不如分组聚合。

方法3：使用INTERSECT（MySQL 8.0.3+支持，逻辑最清晰） #

如果你的MySQL版本在8.0.3及以上，可以用INTERSECT来取多个查询结果的交集，每个子查询对应一个属性条件，语义上最贴合“同时满足所有条件”的需求。

SELECT e.ID, e.NAME
FROM entity e
JOIN entity_props ep ON e.ID = ep.ENTITY_ID
WHERE ep.PROP_ID = 23 AND ep.PROP_VALUE = 'abc'

INTERSECT

SELECT e.ID, e.NAME
FROM entity e
JOIN entity_props ep ON e.ID = ep.ENTITY_ID
WHERE ep.PROP_ID = 24 AND ep.PROP_VALUE = 'def'

INTERSECT

SELECT e.ID, e.NAME
FROM entity e
JOIN entity_props ep ON e.ID = ep.ENTITY_ID
WHERE ep.PROP_ID = 25 AND ep.PROP_VALUE = 'xyz';

说明：INTERSECT会自动去重，确保结果里的实体都是同时满足所有三个条件的，写法上每个条件独立，维护起来很方便。

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