PostgreSQL如何查询与指定乐队存在1度分离关联的其他乐队ID

方法1：IN子查询拼接逻辑 #

直接将查询Foo成员ID的语句作为子查询嵌入过滤条件即可：

SELECT DISTINCT b.id, b.name 
FROM bands AS b
INNER JOIN band_membership AS bm ON b.id = bm.band_id
WHERE bm.musician_id IN (
    SELECT m.id 
    FROM musicians AS m
    INNER JOIN band_membership AS bm ON m.id = bm.musician_id
    WHERE bm.band_id = (SELECT id FROM bands WHERE name = 'Foo')
)
AND b.name != 'Foo' -- 排除原乐队Foo本身
ORDER BY b.id;

这里用DISTINCT实现结果去重，如果你习惯用GROUP BY也可以替换为GROUP BY b.id, b.name，效果一致。

方法2：全JOIN实现（性能更优） #

如果数据量较大，全程用关联查询的执行效率更高，不需要嵌套子查询：

SELECT DISTINCT b_related.id, b_related.name
FROM bands b_foo
-- 关联Foo对应的成员映射
INNER JOIN band_membership bm_foo ON b_foo.id = bm_foo.band_id
-- 关联Foo的所有成员
INNER JOIN musicians m ON bm_foo.musician_id = m.id
-- 关联这些成员加入的所有乐队映射
INNER JOIN band_membership bm_related ON m.id = bm_related.musician_id
-- 关联得到关联乐队的信息
INNER JOIN bands b_related ON bm_related.band_id = b_related.id
WHERE b_foo.name = 'Foo'
AND b_related.name != 'Foo'
ORDER BY b_related.id;

两种方案都可以得到你预期的Bar、Rab两个乐队的查询结果。

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