SQL Oracle用于选择在结果集中显示最高值的列

I have an existing query that loads a specific dataset. Please see sample table for illustration

我有一个加载特定数据集的现有查询。请参阅样本表以获取说明

 Select I.Invoice_ID, I.Invoice_Date, CI.Unit_Rate
 FROM Invoice I, ChargeInvoice CI


Invoice_ID   Invoice_Date  Unit_Rate
A1           05/08/2018    100
A2           04/08/2018    200
A3           03/08/2018    300
B6           04/06/2018    150
C5           04/15/2018    2000

What I need to add a calculated column which displays the MAX or highest value of one field in the result set, that can be named as MAX_UNIT_RATE.

我需要添加一个计算列，该列显示结果集中一个字段的MAX或最高值，可以命名为MAX_UNIT_RATE。

The expected result set is something like this

预期的结果集就是这样的

  Invoice_ID   Invoice_Date    Unit_Rate   Max_Unit_Rate
    A1           05/08/2018    100         2000
    A2           04/08/2018    200         2000
    A3           03/08/2018    300         2000
    B6           04/06/2018    150         2000
    C5           04/15/2018    2000        2000

I tried this, but It is not getting the desired result

我尝试了这个，但它没有得到理想的结果

select IV.INVOICE_ID, IV.INVOICE_DATE , ICV.UNIT_RATE, MAX(ICV.UNIT_RATE) AS MAX_UNIT_RATE
FROM INVOICE_V IV,  
    INVOICE_CHARGE_V ICV
 GROUP BY IV.INVOICE_ID,  IV.INVOICE_DATE, ICV.UNIT_RATE

1 个解决方案

#1

You should be writing your query with correct join conditions. The solution to your problem are window functions:

您应该使用正确的连接条件编写查询。您的问题的解决方案是窗口函数：

SELECT I.Invoice_ID, I.Invoice_Date, CI.Unit_Rate,
       MAX(CI.Unit_Rate) OVER () as MAX_Unit_Rate
FROM Invoice I JOIN
     ChargeInvoice CI
     ON I.Invoice_Id = CI.Invoice_ID -- this is a guess