entity
---
id  name
---
1   one
2   two
3   three

property
---
id  name
---
1   prop1
2   prop2
3   prop3

entity_property
---
entity_id   property_id
---
1           1
1           2
1           3
2           1


我想获得具有至少1和2(但可以有更多)属性的实体。

这是我不喜欢的解决方法:

SELECT entity_property.entity_id,
(GROUP_CONCAT(entity_property.property_id)) as props
FROM `entity_property`
JOIN entity
ON entity_property.entity_id = entity.id
GROUP BY entity.id


它返回:

entity_id props
---
1   1,2,3
2   1


然后,我必须使用服务器语言将其爆炸,然后排除。



该查询返回所有实体的行:

SELECT entity.id
FROM entity
WHERE (1 AND 2) IN
    (SELECT property_id
     FROM entity_property
     LEFT JOIN entity
     ON entity_property.entity_id = entity.id
     WHERE entity_property.entity_id = entity.id)




此查询导致错误:

SELECT entity.id as ent_id
FROM entity
WHERE (1 AND 2) IN
    (SELECT property_id
     FROM entity_property
     LEFT JOIN entity
     ON entity_property.entity_id = entity.id
     WHERE entity_property.entity_id = ent_id)

最佳答案

您可以使用group byhaving获取实体ID:

SELECT ep.entity_id
FROM `entity_property` ep
WHERE ep.property_id IN (1, 2)
GROUP BY ep.entity_id
HAVING COUNT(DISTINCT ep.property_id) = 2;


笔记:


这不需要entity表。
如果在DISTINCT中不允许重复对,则不需要entity_property
对于更多属性,您需要更改WHEREHAVING(其中“ 2”是您要匹配的内容数)。

关于mysql - 在GROUP_CONCAT()中选择位置(1和2),我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/41547744/

10-08 23:10