检索包含所有相关调整细节的最新发票记录

Question

我试图检索所有相关调整细节的最新发票记录(表可能包含重复的发票)。不幸的是，下面的SQL只能检索一行记录。(例如，如果特定的最新发票有多个收费代码，它将只检索其中的一个)

SELECT a.Invoice, a.Shipment_Nbr, a.Invoice_date, b.Adjust_rsn, b.Adjust_date, c.Charge_code, c.Charge_Amt从发票内连接调整b在a.Shipment_Nbr = b.Shipment_Nbr内连接费用c在b.uniq_id = c.uniq_idb.调整日期介于'05-01-2022'和'05-31-2022'之间限定row_number () over (partition by)a.Shipment_NbrORDER BY a.Invoice_date DESC) = 1

参考表上的图像、当前结果和预期结果。表格，当前结果&预期结果

Answer 1

这个应该可以了

select
a.Invoice, b.Shipment_Nbr, a.Invoice_date, b.Adjust_rsn, b.Adjust_date, c.Charge_code, c.Charge_Amt 
FROM
(
select * from Invoicing a
qualify (row_number() over (partition by Shipment_Nbr order by Invoice_date desc)=1)
) a
inner join 
(
select * from Adjust b 
qualify (row_number() over (partition by Shipment_Nbr order by Adjust_date desc)=1)
) b on (a.Shipment_Nbr=b.Shipment_Nbr)
inner join Charge c on (b.Uniq_Id=c.Uniq_Id)
)   -- FROM