如何通过内连接获取tableA各id_A关联的tableB唯一source值

表结构 #

db=> \d tableA
                                Table "public.tableA"
            Column          |           Type           | Collation | Nullable | Default
--------------------------+--------------------------+-----------+----------+---------
 id_A                       | character varying(512)   |           | not null |

db=> \d tableB
                                Table "public.tableB"
              Column          |           Type           | Collation | Nullable | Default
--------------------------+--------------------------+-----------+----------+---------
 id_B                         | character varying(512)   |           | not null |
 source                       | character varying(512)   |           | not null |

db=> \d mappingAB
                                Table "public.mappingAB"
            Column          |           Type           | Collation | Nullable | Default
--------------------------+--------------------------+-----------+----------+---------
 id_A                       | character varying(512)   |           | not null |
 id_B                       | character varying(512)   |           | not null |

示例数据 #

-- tableA
 id_A
---------
 insight1
 insight2
 insight3
 insight4
 insight5

-- mappingAB
id_A        | id_B 
------------+--------
 insight1   | obs1
 insight1   | obs2
 insight2   | obs1
 insight2   | obs2
 insight2   | obs3
 insight3   | obs1

-- tableB
id_B    | source 
--------+--------
 obs1   | AWS
 obs2   | GCP
 obs3   | GCP

需求 #

找出tableA中每个id_A对应的tableB里的唯一source值，统计每个id_A是否关联对应source（1表示关联，0表示未关联），最终以行转列的形式展示所有id_A的结果。

预期输出：

id_A       | AWS    | GCP
-----------+--------+-------
 insight1  | 1      | 1
 insight2  | 1      | 1
 insight3  | 1      | 0
 insight4  | 0      | 0
 insight5  | 0      | 0

错误查询分析 #

你之前执行的查询：

select obs.source, count(obs.source)
from tableB b inner join
mappingAB ab on ab.id_B = obs.id_B
group by obs.source

该查询仅统计了全局范围内每个source的总数量，既没有关联tableA的id_A，也没有实现按id_A分组并行列转换的核心需求。

正确解法 #

方法1：条件聚合（通用写法，无需额外扩展） #

通过条件聚合实现行列转换，同时保留tableA中所有id_A：

select
    a.id_A,
    max(case when b.source = 'AWS' then 1 else 0 end) as AWS,
    max(case when b.source = 'GCP' then 1 else 0 end) as GCP
from tableA a
left join mappingAB ab on a.id_A = ab.id_A
left join tableB b on ab.id_B = b.id_B
group by a.id_A
order by a.id_A;

• 使用左连接确保tableA中无关联的id_A（如insight4、insight5）也能被返回
• max函数用于去重同一id_A对应同一source的多条记录，只保留1或0的标记

方法2：PostgreSQL专属crosstab函数（适合动态列场景） #

如果source值不固定，可使用crosstab实现动态透视，需先启用tablefunc扩展：

-- 仅需执行一次启用扩展
CREATE EXTENSION IF NOT EXISTS tablefunc;

-- 执行透视查询
select * from crosstab(
    'select a.id_A, b.source, 1 as flag
     from tableA a
     left join mappingAB ab on a.id_A = ab.id_A
     left join tableB b on ab.id_B = b.id_B
     group by a.id_A, b.source
     order by a.id_A, b.source',
    'select distinct source from tableB order by source'
) as ct(id_A varchar(512), AWS int, GCP int)
order by id_A;

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