如何在同一张SQL表中基于其他列计算求和及其他聚合字段

问题原因 #

你的原SQL有两个核心错误：

1. 过滤条件不匹配需求：debamt要求的是amount<50的总和，credamt要求的是amount>50的总和，你原语句里写的是AMOUNT < 0、AMOUNT > 0，导致计算结果完全错误。
2. 部分数据库不支持在同层级SELECT子句中直接引用刚定义的别名来计算新字段，直接写OPENAMT - DEBTAMT + CREDAMT可能会报字段不存在的错误。

修复后的SQL（兼容绝大多数支持窗口函数的数据库，比如MySQL8+、PostgreSQL、Oracle等） #

SELECT 
    ID,
    AMOUNT,
    SUM(AMOUNT) OVER () AS OPENAMT,
    SUM(CASE WHEN AMOUNT < 50 THEN AMOUNT ELSE 0 END) OVER () AS DEBAMT,
    SUM(CASE WHEN AMOUNT > 50 THEN AMOUNT ELSE 0 END) OVER () AS CREDAMT,
    SUM(AMOUNT) OVER () 
    - SUM(CASE WHEN AMOUNT < 50 THEN AMOUNT ELSE 0 END) OVER () 
    + SUM(CASE WHEN AMOUNT > 50 THEN AMOUNT ELSE 0 END) OVER () AS CLOSEAMT
FROM INVOICE;

如果你的数据库支持CTE，也可以写得更易读，避免重复计算：

WITH base_cal AS (
    SELECT 
        SUM(AMOUNT) AS total,
        SUM(CASE WHEN AMOUNT <50 THEN AMOUNT ELSE 0 END) AS deb_total,
        SUM(CASE WHEN AMOUNT >50 THEN AMOUNT ELSE 0 END) AS cred_total
    FROM INVOICE
)
SELECT 
    i.ID,
    i.AMOUNT,
    b.total AS OPENAMT,
    b.deb_total AS DEBAMT,
    b.cred_total AS CREDAMT,
    (b.total - b.deb_total + b.cred_total) AS CLOSEAMT
FROM INVOICE i, base_cal b;

两种写法的执行结果都和你给出的期望输出完全一致。

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