如何通过ID数组关联Jobs表与Partners表并输出带合作方名称的任务详情？

没问题！我来帮你搞定这个把ID数组转化为对应名称数组的关联查询需求。核心思路是先把数组拆分成单独的行，关联合作方表拿到名称，再重新聚合成数组，同时还要保证名称的顺序和原ID数组的顺序一致。下面分常见数据库给出实现方案：

PostgreSQL 实现 #

PostgreSQL原生支持数组类型，我们可以用unnest()函数拆分数组，配合WITH ORDINALITY保留原数组的顺序，最后用array_agg()聚合回数组：

SELECT
    j.id,
    j.title AS "Job title",
    array_agg(p.name ORDER BY idx) AS "Partners"
FROM
    Jobs j
-- 拆分数组并记录每个元素的原位置索引
JOIN LATERAL unnest(j.assigned_partners) WITH ORDINALITY AS ap(partner_id, idx)
    ON true
-- 关联合作方表获取名称
JOIN Partners p
    ON p.id = ap.partner_id
GROUP BY
    j.id, j.title
ORDER BY
    j.id;

MySQL 8.0+ 实现 #

如果你的assigned_partners是JSON类型（从数据格式看大概率是），可以用JSON_TABLE()函数把JSON数组拆成行，同样保留顺序索引，再用JSON_ARRAYAGG()聚合：

SELECT
    j.id,
    j.title AS `Job title`,
    JSON_ARRAYAGG(p.name ORDER BY js.seq) AS `Partners`
FROM
    Jobs j
-- 解析JSON数组为行，同时记录每个元素的顺序
JOIN JSON_TABLE(
    j.assigned_partners,
    '$[*]' COLUMNS (
        partner_id VARCHAR(10) PATH '$',
        seq FOR ORDINALITY
    )
) js
-- 关联合作方表
JOIN Partners p ON p.id = js.partner_id
GROUP BY
    j.id, j.title
ORDER BY
    j.id;

关键说明 #

• 两种方案都特意保留了原数组的顺序（通过idx或seq字段排序聚合），确保输出的名称数组和原ID数组顺序完全一致，符合你的期望结果。
• 如果你的数据库是其他类型（比如SQL Server），可以留言告诉我，我再补充对应的实现方式～

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