关联含ID数组的表返回重复根记录，如何解决？

问题描述 #

我尝试关联多张表，提取my_schema.table_a中的每条唯一根记录，但查询返回了重复结果。

我的查询语句：

select
    ta.id,
    ta.table_a_name as "tableName"
from my_schema.table_a ta
left join my_schema.table_b tb
    on (tb.table_a_id = ta.id)
left join my_schema.table_c tc
    on (tc.table_b_id = tb.id)
left join my_schema.table_d td
    on (td.id = any(tc.table_d_ids))
where td.id = any(array[100]);

查询返回结果：

[
    {
        "id": 2,
        "tableName": "Root record 2"
    },
    {
        "id": 2,
        "tableName": "Root record 2"
    }
]

期望结果：

[
    {
        "id": 2,
        "tableName": "Root record 2"
    }
]

建表及插入数据语句：

create schema if not exists my_schema;

create table if not exists my_schema.table_a (
    id serial primary key,
    table_a_name varchar (255) not null
);

create table if not exists my_schema.table_b (
    id serial primary key,
    table_a_id bigint not null references my_schema.table_a (id)
);

create table if not exists my_schema.table_d (
    id serial primary key
);

create table if not exists my_schema.table_c (
    id serial primary key,
    table_b_id bigint not null references my_schema.table_b (id),
    table_d_ids bigint[] not null
);

insert into my_schema.table_a values
    (1, 'Root record 1'),
    (2, 'Root record 2'),
    (3, 'Root record 3');

insert into my_schema.table_b values
    (10, 2),
    (11, 2),
    (12, 3);

insert into my_schema.table_d values
    (100),
    (101),
    (102),
    (103),
    (104);

insert into my_schema.table_c values
    (1000, 10, array[]::int[]),
    (1001, 10, array[100]),
    (1002, 11, array[100, 101]),
    (1003, 12, array[102]),
    (1004, 12, array[103]);

问题原因 #

重复结果的核心原因是关联过程中产生了多条匹配记录：

• table_a中id=2的记录对应table_b中的两条记录（id=10和11）
• 这两条table_b记录分别关联到table_c中包含100的记录（id=1001和1002）
• 最终关联table_d时，每条匹配都会生成一条结果，导致table_a的同一条记录被返回多次

解决方案 #

方法1：使用DISTINCT去重 #

直接在SELECT后添加DISTINCT关键字，过滤重复的结果行：

select distinct
    ta.id,
    ta.table_a_name as "tableName"
from my_schema.table_a ta
left join my_schema.table_b tb
    on (tb.table_a_id = ta.id)
left join my_schema.table_c tc
    on (tc.table_b_id = tb.id)
left join my_schema.table_d td
    on (td.id = any(tc.table_d_ids))
where td.id = any(array[100]);

方法2：使用EXISTS子查询（推荐） #

通过子查询判断是否存在符合条件的关联记录，避免生成重复行，性能通常更优：

select
    ta.id,
    ta.table_a_name as "tableName"
from my_schema.table_a ta
where exists (
    select 1
    from my_schema.table_b tb
    join my_schema.table_c tc on tc.table_b_id = tb.id
    where tb.table_a_id = ta.id
      and 100 = any(tc.table_d_ids)
);

方法3：使用IN子查询 #

先筛选出符合条件的table_a_id集合，再查询table_a：

select
    ta.id,
    ta.table_a_name as "tableName"
from my_schema.table_a ta
where ta.id in (
    select tb.table_a_id
    from my_schema.table_b tb
    join my_schema.table_c tc on tc.table_b_id = tb.id
    where 100 = any(tc.table_d_ids)
);

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