mysql - 有实体表和有属性的单独表，如何构建优雅的SQL查询

Question

MySQL数据库中有表

entity
----------
ID NAME
1  entity1
2  entity2
3  entity3

entity_props
----------
ENTITY_ID PROP_ID PROP_VALUE
1          23      abc
1          24      def
1          25      xyz

当我需要选择具有属性值 23="abc"、24="def"和 25="xyz"的所有实体时，我使用此类请求

SELECT ID 
FROM entity 
WHERE PROP_ID=23 AND PROP_VALUE="abc" AND ID IN 
    (SELECT ENTITY_ID FROM entity_props WHERE PROP_ID=24 AND PROP_VALUE="def" and ENTITY_ID IN
    (SELECT ENTITY_ID FROM entity_props WHERE PROP_ID=25 AND PROP_VALUE="xyz"))

但是当属性太多时，它看起来很糟糕。你能建议如何简化它吗？

提前致谢!

Answer 1

嗯。 . . group by 和 having 怎么样？

select entity_id
from entity_props ep
where (prop_id, prop_value) in ((23, 'abc'), (24, 'def'), (25, 'xyz'))
group by entity_id
having count(distinct prop_id) = 3;