PostgreSQL左连接返回多行问题：合并OLI或取首行的方法

这是关联查询里非常常见的问题，我给你两种精准匹配需求的解决思路：

方案一：将同一订单的OLI合并为集合返回 #

PostgreSQL提供了专门的聚合函数来把多行结果合并成集合，常用的有两种：

1. 使用array_agg()生成数组 #

如果你想把OLI的某个字段（比如SKU、数量）合并成数组，或者把整个OLI行合并成数组，可以这么写：

SELECT 
    o.*,
    -- 合并特定字段为数组，比如OLI的SKU
    array_agg(oli.sku) AS oli_skus,
    -- 合并整个OLI行成数组
    array_agg(oli) AS all_olis
FROM orders o
LEFT JOIN order_line_items oli ON o.id = oli.order_id
GROUP BY o.id; -- 按订单分组，确保每个订单只返回一行

如果担心重复的OLI，可以加上DISTINCT：array_agg(DISTINCT oli.sku)；如果订单没有匹配的OLI，想返回空数组而不是NULL，可以用COALESCE(array_agg(oli), '{}'::json[])来处理。

2. 使用json_agg()生成JSON数组 #

如果需要更易读的JSON格式结果，推荐用json_agg()：

SELECT 
    o.*,
    json_agg(oli) AS oli_json_array
FROM orders o
LEFT JOIN order_line_items oli ON o.id = oli.order_id
GROUP BY o.id;

这个会把所有匹配的OLI转换成一个JSON数组，非常适合后续应用层解析。

方案二：仅返回每个订单的首个OLI #

如果你只需要保留每个订单的第一条OLI（可以自定义“首个”的规则，比如按ID、创建时间排序），有两种简洁的实现方式：

1. 使用PostgreSQL专属的DISTINCT ON语法 #

这是PostgreSQL特有的高效写法，语法简洁性能好：

SELECT DISTINCT ON (o.id)
    o.*,
    oli.*
FROM orders o
LEFT JOIN order_line_items oli ON o.id = oli.order_id
-- 必须先按订单ID排序，再按你定义的“首个”规则排序（比如OLI的创建时间）
ORDER BY o.id, oli.created_at ASC;

注意：DISTINCT ON后面的字段（这里是o.id）必须是ORDER BY子句的第一个字段，后面的排序字段决定了哪条OLI会被选为“首个”。

2. 使用窗口函数ROW_NUMBER() #

如果需要更灵活的逻辑（比如过滤某些OLI后再取首个），窗口函数是更好的选择：

SELECT *
FROM (
    SELECT 
        o.*,
        oli.*,
        -- 按订单分组，给每个OLI编号，按创建时间升序排，首个就是1
        ROW_NUMBER() OVER (PARTITION BY o.id ORDER BY oli.created_at ASC) AS rn
    FROM orders o
    LEFT JOIN order_line_items oli ON o.id = oli.order_id
) AS subquery
WHERE rn = 1; -- 只保留每个订单的第一条OLI

这种方式适合复杂场景，比如你需要先过滤掉已取消的OLI，再取剩余的首个。

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