You need to enable JavaScript to run this app. PostgreSQL如何使用array_agg和数组包含运算符匹配两张表姓名数据
优惠活动
大模型
产品
解决方案
定价
更多

PostgreSQL如何使用array_agg和数组包含运算符匹配两张表姓名数据

PostgreSQL双表姓名关联查询解决方案

1. 生成t2全姓名字段的一维数组

你之前的写法报错是因为将多个嵌套数组直接塞入同一个ARRAY构造器,导致出现text和text[]类型不匹配的问题,且硬编码键名无法适配动态场景。以下是支持动态键名的一维数组生成语句:

SELECT 
    t2.id AS t2_id,
    t2.feat_id,
    -- 合并所有名称为一维文本数组,自动过滤空值
    ARRAY(
        SELECT unnest(
            ARRAY[t2.name_1, t2.name_2] 
            -- 提取alt_names中所有键对应的值,无需指定键名
            || ARRAY(SELECT value FROM jsonb_each_text(t2.alt_names->0))
        )
        WHERE unnest IS NOT NULL
    ) AS t2_names
FROM t2;

生成的t2_names格式完全符合预期,示例结果:

{'Natasha Romanoff','Black Widow','Black Widow','Natalia Alianovna Romanova','Наталья "Наташа Романоф"'}

2. 数组匹配与关联表生成

2.1 t1数组构造

t1单条记录本身对应一组姓名,无需聚合可直接构造数组:

SELECT 
    id AS t1_id,
    fid AS t1_fid,
    name_1 AS t1_name_1,
    name_rank,
    ARRAY[name_1, name_2] AS t1_names
FROM t1;

2.2 匹配规则与关联表生成

使用数组重叠运算符&&判断两组姓名是否存在共同元素,用DISTINCT ON取每个t2匹配到的name_rank最小的t1记录:

-- 生成最终关联表
CREATE TABLE t1_linked_t2 AS
SELECT DISTINCT ON (t2.id)
    t1.id AS id,
    t1.fid AS t1_fid,
    t1.name_1 AS t1_name_1,
    t1.name_rank,
    t2.name_1 AS t2_name,
    t2.id AS t2_id
FROM t1
-- 先关联特征id缩小匹配范围,大幅提升查询性能
JOIN t2 ON t1.fid = t2.feat_id
WHERE 
    -- 判断两个姓名数组是否存在共同元素
    ARRAY[t1.name_1, t1.name_2] && (
        SELECT ARRAY(
            SELECT unnest(
                ARRAY[t2.name_1, t2.name_2] || ARRAY(SELECT value FROM jsonb_each_text(t2.alt_names->0))
            )
            WHERE unnest IS NOT NULL
        )
    )
-- 按t2分组,优先取name_rank最小的匹配记录
ORDER BY t2.id, t1.name_rank ASC;

查询关联表验证结果:

SELECT * FROM t1_linked_t2;

内容的提问来源于stack exchange,提问作者Holly

相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级,最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分), 限时 9.9 元起

最近更新时间:2026.10.11 03:24:03