postgresql - 将连接的表标题聚合成逗号分隔的字符串，按创建时间排序

Question

我正在尝试选择一个列，该列是所有连接行标题的聚合，按连接行的 created_at 时间排序。但是，无论 INNER JOIN 中的 ORDER BY 是什么，标题字符串始终保持不变。

这是我的查询:

                SELECT
                    id AS assignment_id,
                    ARRAY_TO_STRING(ARRAY(SELECT DISTINCT UNNEST(ARRAY_AGG(_outlets.title))), ',', '') AS outlets,
                FROM
                    assignments
                LEFT JOIN (
                        SELECT
                            assignment_outlets.created_at AS created_at,
                            outlet_id,
                            assignment_id,
                            title
                        FROM assignment_outlets
                        INNER JOIN outlets ON outlets.id = assignment_outlets.outlet_id
                        ORDER BY assignment_outlets.created_at DESC
                    )
                    AS _outlets
                    ON _outlets.assignment_id = assignments.id
                GROUP BY assignments.id;

我正在尝试获取结果 'title1,title2,title3,title4,...'，其中它们从左到右按从旧到新的顺序排列。

Answer 1

您可以简单地使用string_agg，不需要子查询:

SELECT a.id AS assignment_id, string_agg(o.title, ',' ORDER BY ao.created_at DESC) AS outlets
FROM assignments a
LEFT JOIN assignment_outlets ao ON (ao.assignment_id = i.id)
LEFT JOIN outlets o ON (o.id = ao.outlet_id)
GROUP BY a.id;