SQL查询使用双聚合函数：生成含DIFF的INVOICE表查询求助

嘿，刚入门SQL碰到这种细节问题太正常啦，我来帮你搞定～

首先得先搞清楚你要的DIFF字段到底是什么：从你的描述来看，应该是每条发票的金额和整张表平均发票金额的差值对吧？那你之前用SUM(INV_AMOUNT - AVG(INV_AMOUNT))的问题在于，这个表达式会把所有行的差值加起来，而数学上这个总和永远是0（因为所有金额的总和等于平均值乘以行数，相减后总和为0），这肯定不是你想要的结果。

方法一：基于你现有语句修改 #

你已经写出了获取平均金额的子查询，直接在SELECT里用当前行的INV_AMOUNT减去这个平均值就行，不用加SUM：

SELECT 
    INV_NUM, 
    INV_AMOUNT, 
    (SELECT AVG(INV_AMOUNT) FROM INVOICE) AS AVG_INV,
    INV_AMOUNT - (SELECT AVG(INV_AMOUNT) FROM INVOICE) AS DIFF
FROM INVOICE;

这样每条记录都会显示自己的金额与整体平均值的差值，完全符合你的需求。

方法二：用窗口函数更优雅（推荐） #

如果你的数据库支持窗口函数（比如MySQL 8.0+、PostgreSQL、SQL Server等），可以用AVG() OVER()来一次性计算全局平均值，不用重复写子查询，代码更简洁，效率也更高：

SELECT 
    INV_NUM, 
    INV_AMOUNT,
    AVG(INV_AMOUNT) OVER() AS AVG_INV,
    INV_AMOUNT - AVG(INV_AMOUNT) OVER() AS DIFF
FROM INVOICE;

AVG(INV_AMOUNT) OVER()会计算整个INVOICE表的平均金额，并且把这个值赋给每一行，这样直接做减法就能得到每条记录的差值。

为什么你的原写法不行？ #

再啰嗦两句帮你理解：SUM(INV_AMOUNT - AVG(INV_AMOUNT))本质是把所有行的(金额-平均值)加起来，而根据平均值的定义：
SUM(INV_AMOUNT) = AVG(INV_AMOUNT) * COUNT(*)
所以代入后：
SUM(INV_AMOUNT - AVG) = SUM(INV_AMOUNT) - COUNT(*) * AVG = 0
这个结果对单条记录来说没有意义，所以才会不符合你的预期～

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