如何在SQLite中返回关联表行的数组形式？

当然可行！SQLite自带的聚合函数就能帮你实现这个需求，完全不用在应用代码里加额外的过滤逻辑，下面给你两种简洁的写法，按需选择：

方式1：生成自定义格式的字符串数组 #

如果你就想要示例里那种[1 | abc | 1, 2 | def | 1]的自定义格式，可以用GROUP_CONCAT函数来拼接：

SELECT 
    a.identifier,
    a.name,
    '[' || GROUP_CONCAT(b.identifier || ' | ' || b.name || ' | ' || b.fk_a, ', ') || ']' AS b_records
FROM table_a a
LEFT JOIN table_b b ON a.identifier = b.fk_a
GROUP BY a.identifier, a.name;

• LEFT JOIN确保即使table_a里的行没有对应的table_b记录，也会被返回（此时b_records会是[]）
• GROUP_CONCAT把每个关联的table_b行按你要的格式拼接成字符串，再用||运算符包裹成数组的样子

方式2：生成标准JSON数组（推荐） #

如果希望返回的数组格式更规范，方便后续在应用中解析，建议用SQLite的JSON聚合函数（需要SQLite 3.25.0及以上版本）：

SELECT 
    a.identifier,
    a.name,
    JSON_ARRAYAGG(JSON_OBJECT(
        'identifier', b.identifier,
        'name', b.name,
        'fk_a', b.fk_a
    )) AS b_records
FROM table_a a
LEFT JOIN table_b b ON a.identifier = b.fk_a
GROUP BY a.identifier, a.name;

这个语句会把每个table_b的行转成JSON对象，再聚合成一个JSON数组，返回结果类似：

1 | ABC | [{"identifier":1,"name":"abc","fk_a":1},{"identifier":2,"name":"def","fk_a":1}]

这种格式比自定义字符串更稳定，不容易因为特殊字符（比如|或者逗号）出现解析问题。

注意事项 #

• 如果你的SQLite版本比较旧（低于3.25.0），JSON相关函数不支持，就用第一种GROUP_CONCAT的写法
• GROUP BY子句里要包含table_a的所有非聚合列，这里因为identifier是主键，其实只GROUP BY a.identifier也能运行，但加上a.name更符合SQL标准

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