Oracle SQL需求：添加显示结果集最大Unit_Rate的计算列

问题描述 #

你需要给关联Invoice和ChargeInvoice的查询结果新增MAX_UNIT_RATE列，用来展示整个结果集里Unit_Rate字段的全局最大值，且每一行都要显示这个值。

原查询及现有结果 #

你的原查询语句：

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

预期结果 #

你希望得到每一行都携带全局Unit_Rate最大值的输出：

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

你尝试的SQL及问题所在 #

你试了下面这段SQL，但没得到预期结果：

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

问题出在GROUP BY子句：你把Invoice_ID、Invoice_Date和Unit_Rate都作为分组依据，这会让MAX(ICV.UNIT_RATE)只计算当前分组的最大值——也就是每一行自己的Unit_Rate值，自然没法得到全局最大值。

解决方案 #

这里提供两种常用实现方式，可根据你的数据库版本选择：

方法1：使用窗口函数（推荐） #

如果你的数据库支持SQL:2003标准及以上（比如MySQL 8.0+、PostgreSQL、SQL Server、Oracle等），窗口函数是最简洁高效的方案：

SELECT 
    I.Invoice_ID, 
    I.Invoice_Date, 
    CI.Unit_Rate,
    MAX(CI.Unit_Rate) OVER () AS MAX_UNIT_RATE
FROM Invoice I, ChargeInvoice CI

解释：OVER ()表示聚合函数的窗口范围是整个结果集，MAX()会计算所有行的Unit_Rate最大值，再把这个值附加到每一行上。

方法2：使用子查询获取全局最大值 #

如果你的数据库不支持窗口函数（比如MySQL 5.x版本），可以用子查询先算出全局最大值，再将其作为常量列加入结果：

SELECT 
    I.Invoice_ID, 
    I.Invoice_Date, 
    CI.Unit_Rate,
    (SELECT MAX(Unit_Rate) FROM ChargeInvoice) AS MAX_UNIT_RATE
FROM Invoice I, ChargeInvoice CI

解释：子查询(SELECT MAX(Unit_Rate) FROM ChargeInvoice)会单独计算ChargeInvoice表中Unit_Rate的最大值，这个值会被当作固定值添加到主查询的每一行结果里。

⚠️ 注意：如果你的原查询需要关联两张表（比如通过Invoice_ID匹配，原示例未写连接条件），记得加上JOIN条件避免笛卡尔积，示例如下：

-- 带正确连接条件的窗口函数写法
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

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