Postgres使用JOIN关联表实现查询返回array类型列的问题求解

问题原因 #

你当前查询返回多行重复卡牌数据的核心问题是GROUP BY子句中错误加入了printings.id字段，相当于按每个独立的发行版本分组，有多少个发行版本就会生成多少个分组，自然会返回6行结果。

正确查询语句 #

你使用的array_agg是PostgreSQL的语法，按以下写法调整即可得到预期的单条卡牌数据+所有发行信息聚合的结果：

SELECT 
    cards.*,
    array_agg(printings.*) FILTER (WHERE printings.id IS NOT NULL) AS printing_information
FROM cards
LEFT JOIN printings ON cards.id = printings.card_id
WHERE cards.name = 'C1' -- SQL标准要求字符串常量用单引号包裹
GROUP BY cards.id -- 仅按卡牌表主键分组即可，cards.id是唯一键，cards.*所有字段可直接被选中
ORDER BY cards.id;

如果使用MySQL环境，不支持array_agg函数，可以改用GROUP_CONCAT实现字段拼接：

SELECT 
    cards.*,
    GROUP_CONCAT(
        CONCAT('id:',printings.id,',set:',printings.set,',其他字段:',printings.xxx)
        SEPARATOR '|'
    ) AS printing_information
FROM cards
LEFT JOIN printings ON cards.id = printings.card_id
WHERE cards.name = 'C1'
GROUP BY cards.id;

常见疑问解答 #

1. 该需求完全适合通过单条SQL实现，这是关系型数据库针对一对多关系的标准聚合查询场景，是原生支持的能力。
2. 性能层面，单查询方案远优于多次查询+后置数据处理：
   • 避免了多次数据库连接交互的网络开销，批量查询多卡牌时优势更明显
   • 数据库内置的聚合算子经过深度优化，执行效率远高于应用层手动遍历合并数据
   • 批量查询时仅需要调整WHERE条件（例如改为WHERE cards.name IN ('C1','C2','C3')）即可，代码实现更简洁，扩展性更强。

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