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 by
和having
获取实体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
。对于更多属性,您需要更改
WHERE
和HAVING
(其中“ 2”是您要匹配的内容数)。关于mysql - 在GROUP_CONCAT()中选择位置(1和2),我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/41547744/