PostgreSQL如何查询存在符合指定子属性条件的父ID

涉及表结构 #

现有两张表结构如下：

• parents 父表，字段：id、name
• attributes 子属性表（EAV模型），字段：id、child_id、parent_id、attribute、attribute_value

筛选规则 #

需要返回符合要求的parent_id：该父ID关联的子属性记录中，必须同时满足两个属性要求：存在intelligence=5、存在health=4，覆盖两种合法场景：

• 同一个子节点（同child_id）下同时存在上述两个属性
• 两个不同子节点分别满足单个属性要求

可直接使用的SQL写法 #

写法1：条件聚合（推荐，性能最优） #

按parent_id分组，直接统计分组内两个属性条件是否都命中，不需要额外判断属性是否属于同一个子节点，天然覆盖所有要求的场景：

SELECT parent_id
FROM attributes
WHERE 
  (attribute = 'intelligence' AND attribute_value = '5')
  OR (attribute = 'health' AND attribute_value = '4')
GROUP BY parent_id
HAVING 
  COUNT(CASE WHEN attribute = 'intelligence' AND attribute_value = '5' THEN 1 END) > 0
  AND COUNT(CASE WHEN attribute = 'health' AND attribute_value = '4' THEN 1 END) > 0;

如果需要同时获取父表的name等字段，直接关联parents表即可：

SELECT p.id, p.name
FROM parents p
INNER JOIN (
  SELECT parent_id
  FROM attributes
  WHERE 
    (attribute = 'intelligence' AND attribute_value = '5')
    OR (attribute = 'health' AND attribute_value = '4')
  GROUP BY parent_id
  HAVING 
    COUNT(CASE WHEN attribute = 'intelligence' AND attribute_value = '5' THEN 1 END) > 0
    AND COUNT(CASE WHEN attribute = 'health' AND attribute_value = '4' THEN 1 END) > 0
) match_result ON p.id = match_result.parent_id;

写法2：交集查询 #

逻辑直白，分别查出满足单个属性条件的parent_id，取两个结果集的交集即可，同样覆盖所有合法场景：

SELECT parent_id FROM attributes WHERE attribute = 'intelligence' AND attribute_value = '5'
INTERSECT
SELECT parent_id FROM attributes WHERE attribute = 'health' AND attribute_value = '4';

注意点 #

• 如果attribute_value字段是数值类型，把SQL里'5'、'4'的单引号去掉，直接写数字即可，避免隐式类型转换影响性能
• 数据量较大时，给attributes表建(attribute, attribute_value, parent_id)的联合索引，查询速度会有明显提升
• 不需要额外写逻辑区分两个属性是否属于同一个子节点，上述两种写法会自动命中两类符合要求的场景

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