SQLite实现子查询多行结果转为列内列表的方法

你需要查询符合订阅条件的rewards记录，同时把关联files表中匹配的多个file_name合并成列表作为images列返回，但原查询的子查询只能返回单个值，无法获取所有匹配结果。以下是针对不同数据库的解决方案：

核心思路 #

通过表关联+字符串聚合函数替代子查询，将多个匹配的file_name合并为一个字符串列表，同时按rewards的字段分组确保每条rewards记录只返回一行。

方案一：MySQL/SQLite 适用 #

SELECT 
    a.uid, 
    a.reward_title, 
    a.reward_content, 
    a.reward_date as date_time, 
    a.reward_pinned as pinned,
    -- 用逗号分隔合并多个文件名，可自定义分隔符
    GROUP_CONCAT(c.file_name SEPARATOR ', ') as images
FROM rewards a
INNER JOIN subscriptions b 
    ON b.creator_uid = a.creator_uid 
    AND b.tier_hierarchy = a.tier_hierarchy
-- 用LEFT JOIN保证没有关联文件的rewards也能被查询到，不需要的话可换成INNER JOIN
LEFT JOIN files c 
    ON c.reference_uid = a.uid 
    AND c.reference_type = ?
WHERE b.user_uid = ?
-- 必须按rewards的所有查询字段分组，确保分组逻辑正确
GROUP BY a.uid, a.reward_title, a.reward_content, a.reward_date, a.reward_pinned

方案二：PostgreSQL 适用 #

PostgreSQL使用STRING_AGG函数替代GROUP_CONCAT：

SELECT 
    a.uid, 
    a.reward_title, 
    a.reward_content, 
    a.reward_date as date_time, 
    a.reward_pinned as pinned,
    STRING_AGG(c.file_name, ', ') as images
FROM rewards a
INNER JOIN subscriptions b 
    ON b.creator_uid = a.creator_uid 
    AND b.tier_hierarchy = a.tier_hierarchy
LEFT JOIN files c 
    ON c.reference_uid = a.uid 
    AND c.reference_type = ?
WHERE b.user_uid = ?
GROUP BY a.uid, a.reward_title, a.reward_content, a.reward_date, a.reward_pinned

关键说明 #

1. 关联方式：用LEFT JOIN而非子查询，避免子查询只能返回单个值的限制，同时保留没有关联文件的rewards记录。
2. 聚合函数：不同数据库的字符串聚合函数不同，根据你使用的数据库选择对应的函数即可。
3. 分组逻辑：必须将rewards表中所有查询的字段都加入GROUP BY，否则会出现分组错误（部分数据库允许非分组字段，但严格模式下会报错）。

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