PostgreSQL递归CTE按客户分组拼接未付大额发票问题求解

你的递归CTE逻辑存在4处硬伤，直接导致结果异常：

• CTE定义顺序违规：PostgreSQL的WITH子句中对象需要先定义再引用，你在递归CTElista_facturas内部提前引用了后续才定义的factura_cliente，本身就存在执行逻辑错误
• 递归锚点设置错误：没有将每个客户的第一条符合条件发票作为唯一递归起点，而是把所有符合条件发票都作为起始行，导致递归从每一行单独生成拼接分支，产生大量重复数据
• 递归关联逻辑错误：在递归迭代阶段重复计算ROW_NUMBER()、COUNT()窗口函数，迭代过程中窗口函数会基于临时关联结果重新生成序号，完全打乱了预先的排序编号，导致关联条件f.numero_fila = l.numero_fila + 1完全失效
• 结果未做最终过滤：递归执行过程会逐次生成「1张发票」「2张发票」...「全部发票」的所有中间拼接结果，你没有筛选每个客户拼接完成的最终行，因此返回了大量单条/部分拼接的无效记录

注：你提供的样例数据中所有payed='N'的发票金额均低于28000欧元，执行样例数据不会返回结果，替换为真实业务数据即可正常输出。

WITH RECURSIVE
-- 提前筛选符合条件的发票，生成固定序号，递归阶段不重复计算窗口值
factura_cliente AS(
    SELECT
        cust_no,
        invoice_no,
        ROW_NUMBER() OVER (PARTITION BY cust_no ORDER BY invoice_no) AS numero_fila,
        COUNT(*) OVER (PARTITION BY cust_no) AS total_facturas
    FROM erp.tb_invoice i
    WHERE payed = 'N'
        AND tot_amount > 28000
),
lista_facturas AS (
    -- 锚点：每个客户只取序号为1的发票作为递归起点
    SELECT
        cust_no,
        numero_fila,
        total_facturas,
        CAST(invoice_no AS TEXT) AS resultado
    FROM factura_cliente
    WHERE numero_fila = 1

    UNION ALL

    -- 递归：通过固定序号关联下一张发票，逐段拼接字符串
    SELECT
        f.cust_no,
        f.numero_fila,
        f.total_facturas,
        CAST(l.resultado || ',' || f.invoice_no AS TEXT) AS resultado
    FROM factura_cliente f
    INNER JOIN lista_facturas l
        ON l.cust_no = f.cust_no
        AND f.numero_fila = l.numero_fila + 1
)
-- 最终结果只保留拼接完成的行，按要求设置别名、排序
SELECT 
    cust_no AS nombre_cliente,
    resultado,
    total_facturas
FROM lista_facturas
WHERE numero_fila = total_facturas
ORDER BY total_facturas DESC;

如果不是刻意练习递归CTE，PostgreSQL内置的string_agg聚合函数可以用更短的代码实现完全相同的效果：

SELECT
    cust_no AS nombre_cliente,
    STRING_AGG(invoice_no, ',' ORDER BY invoice_no) AS resultado,
    COUNT(*) AS total_facturas
FROM erp.tb_invoice
WHERE payed = 'N'
    AND tot_amount > 28000
GROUP BY cust_no
ORDER BY total_facturas DESC;

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