PostgreSQL单表自连接查询求助：匹配同Item同arrayIndex的特定数据

需求说明 #

查询Items表中，**存在同item、同arrayIndex的factId为'fact:171'且value='false'**的所有factId为'fact:170'的value值。

原查询问题分析 #

你写的查询语句里多了一个完全不必要的INNER JOIN Items c，而且c表的筛选条件和l表完全一致（都是factid='fact:170'），这会触发笛卡尔积——比如某个item下有3条fact:170的记录，连接c表后就会变成3×3=9条重复数据，这就是你看到大量重复结果的原因。

正确查询写法 #

方法1：精简JOIN写法 #

只关联需要的fact:171记录，用DISTINCT去重（如果同一个value对应多条匹配的fact:171记录，会重复输出，按需选择是否保留）：

SELECT DISTINCT l.value
FROM Items l
INNER JOIN Items p 
    ON p.item = l.item 
    AND p.arrayindex = l.arrayindex
    AND p.factid = 'fact:171' 
    AND p.value = 'false'
WHERE l.factid = 'fact:170'

方法2：EXISTS子查询（性能更优） #

用EXISTS判断是否存在匹配记录，数据库找到第一条匹配就会停止扫描，避免不必要的连接操作，性能更好：

SELECT l.value
FROM Items l
WHERE l.factid = 'fact:170'
AND EXISTS (
    SELECT 1
    FROM Items p
    WHERE p.item = l.item
      AND p.arrayindex = l.arrayindex
      AND p.factid = 'fact:171'
      AND p.value = 'false'
)

性能优化建议 #

为了让查询更快，建议创建复合索引，覆盖查询用到的字段：

CREATE INDEX idx_items_item_arrayindex_factid_value ON Items(item, arrayindex, factid, value);

这个索引能让数据库直接定位到符合条件的记录，避免全表扫描，大幅提升查询速度。

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