试图让CTE在PostgreSQL中工作

Question

我是SQL的新手，我试图在没有任何运气的情况下进行递归查询来在Posttresql上工作。

I have a table tb_invoice with these columns:

invoice_no	cust_no	payed	tot_amount
"F0000C000400200"	"C0004"	"Y"	28786.7
"F0000C000400201"	"C0004"	"N"	5624.29
"F0000C000400202"	"C0004"	"Y"	25675.54
"F0000C000400203"	"C0004"	" y“	35479.72
” F0000C000400207“”	“ C0004”	Y“ Y”	23497.47
“ F0000C000500212	”	”	14754.03
C0005“”	F0000C000500213	C0005 “ N”	，

“ 每个CUST_NO的一行28.000 eur。并订购发票数量的结果。它应该看起来像这样（在列中Nombre_cliente中应该出现cust_no）。

，

但我无法完成代码。我已经尝试了多种方法，但是它总是以重复值或每行的一张发票返回行。

这是我尝试过的一些代码。我知道我的错误是在我对工会条款的条件下，我找不到解决方案...

    WITH RECURSIVE lista_facturas AS (
    SELECT
        cust_no,
        ROW_NUMBER() OVER (PARTITION BY cust_no) AS numero_fila,
        COUNT(*) OVER (PARTITION BY cust_no) AS max_numero_fila,
        CAST (invoice_no AS TEXT) AS resultado
    FROM 
        factura_cliente
-- I have tried to add multiple WHERE clauses, but they all fail...
    UNION ALL
    SELECT
        f.cust_no,
        ROW_NUMBER() OVER (PARTITION BY f.cust_no) AS numero_fila,
        COUNT(*) OVER (PARTITION BY f.cust_no) AS max_numero_fila,
        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
               AND f.numero_fila <= l.max_numero_fila)

    -- Here I also tried 'l.invoice_no <> f.invoice_no' and other combinations
), factura_cliente AS(
    SELECT
        cust_no,
        invoice_no,
        ROW_NUMBER() OVER (PARTITION BY cust_no) AS numero_fila,
        COUNT(*) OVER (PARTITION BY cust_no) AS max_numero_fila
    FROM erp.tb_invoice i
    WHERE payed = 'N'
        AND tot_amount > 28000
)
SELECT 
    cust_no,
    resultado
FROM
    lista_facturas
;

Answer 1

使用String_agg函数创建昏迷的发票列表。

SELECT 
  cust_no, 
  STRING_AGG(invoice_no,',' ORDER BY invoice_no) invoice_list  
FROM tb_invoice
WHERE payed = 'N'
      AND tot_amount > 28000
GROUP BY cust_no;