SQL中含嵌套值的表如何关联？已有拆分语句求关联方案

表结构 #

t1表 #

t1_profile_id | nested_t2_ids
--------------|--------------
            1 |       15 | 16
            2 |             3
            3 |          NULL

t2表 #

t2_id |      name
------|----------
   15 |    Helena
   16 |     Chris
    3 |       Jim

期望输出 #

profile_id | t2_ids |    name
-----------|--------|--------
         1 |     15 |  Helena
         1 |     16 |   Chris
         2 |      3 |     Jim

现有查询（已实现前两列） #

select t1.t1_profile_id , unnest(string_to_array(t1.nested_t2_ids, ' | ')) as t2_ids
from t1 

完整关联查询方案 #

你需要先处理拆分后t2_ids的空格问题，转换为与t2.t2_id匹配的数值类型，再通过JOIN关联t2表，同时过滤掉无关联数据的行：

select 
    t1.t1_profile_id as profile_id,
    trim(t2_ids_raw) as t2_ids,
    t2.name
from t1
cross join unnest(string_to_array(t1.nested_t2_ids, ' | ')) as t2_ids_raw
join t2 on trim(t2_ids_raw)::int = t2.t2_id
where t1.nested_t2_ids is not null

关键点说明 #

1. trim()去除拆分后字符串两端的空格，避免空格导致关联失败
2. ::int将处理后的字符串转换为整数类型，与t2.t2_id类型匹配
3. where t1.nested_t2_ids is not null过滤掉无关联数据的行（比如t1_profile_id=3的记录）
4. 使用cross join替代select内直接调用unnest，让行拆分逻辑更清晰

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