PostgreSQL中SELECT左连接如何仅返回唯一匹配行

架构老林

2026-8-27

PostgreSQL 筛选动物专属独有能力查询方案

场景说明

涉及三张业务表：

animal：动物基础信息表
ability：能力项基础信息表
can：动物与能力的多对多映射表
需求为排除多个动物共有的能力，仅返回每个动物独有的专属能力。

基础数据参考

animal表共3条记录：id=1对应name=dog，id=2对应name=bird，id=3对应name=fish
全量查询语句：
```
select * from animal;
```
ability表共5条记录：id=1对应breathe below the surface，id=2对应fly，id=3对应swim，id=4对应bark，id=5对应see
全量查询语句：
```
select * from ability;
```

全量关联查询所有动物对应能力的语句：

select
    animal.name as animal,
    ability.name as can,
    animal.id as animal_id,
    ability.id as ability_id
from can
inner join animal on animal.id = can.animal_id
inner join ability on ability.id = can.ability_id;

该查询返回8条结果，其中swim、see为多个动物共有的能力，属于需要排除的范围。

期望结果

共3条专属能力匹配记录：

dog 对应 bark
bird 对应 fly
fish 对应 breathe below the surface

实现代码

核心逻辑为先按能力分组统计关联的动物数量，仅保留仅关联1个动物的独有能力，再关联业务表获取最终结果：

WITH unique_abilities AS (
    SELECT ability_id
    FROM can
    GROUP BY ability_id
    HAVING COUNT(DISTINCT animal_id) = 1
)
SELECT
    a.name AS animal,
    ab.name AS can,
    a.id AS animal_id,
    ab.id AS ability_id
FROM can c
INNER JOIN unique_abilities ua ON c.ability_id = ua.ability_id
INNER JOIN animal a ON a.id = c.animal_id
INNER JOIN ability ab ON ab.id = c.ability_id;