如何在PostgreSQL的group by子句中将一条记录保留在特定的列中，并将其他记录值设为0？

Question

我有一组这样的数据

​

​

结果应该如下所示

​

​

我的问题

SELECT max(pi.pi_serial) AS proforma_invoice_id,
       max(mo.manufacturing_order_master_id) AS manufacturing_order_master_id,
       max(pi.amount_in_local_currency) AS sales_value,
FROM proforma_invoice pi
JOIN schema_order_map som ON pi.pi_serial = som.pi_id
LEFT JOIN manufacturing_order_master mo ON som.mo_id = mo.manufacturing_order_master_id
WHERE to_date(pi.proforma_invoice_date, 'DD/MM/YYYY') BETWEEN to_date('01/03/2021', 'DD/MM/YYYY') AND to_date('19/04/2021', 'DD/MM/YYYY')
  AND pi.pi_serial in (9221,
                       9299)
GROUP BY mo.manufacturing_order_master_id,
         pi.pi_serial
ORDER BY pi.pi_serial

Answer 1

选项1:在Crystal Reports中创建一个"Running Total“字段，对每个"proforma_invoice_id”只汇总一个"sales_value“。

选项2:向Postgresql查询添加一个helper列，如下所示：

case 
    when row_number() 
         over (partition by proforma_invoice_id 
               order by manufacturing_order_master_id) 
         = 1 
then sales_value 
else 0 
end 
    as sales_value 

我为你准备了一个示例的this SQLFiddle (当然也鼓励你在你的下一个数据库查询相关问题上也这样做:-)