如何在SQL查询的条件子句中存储中间计算结果？

你之前的写法问题出在WHERE子句里不能直接定义聚合计算的别名——WHERE是分组前用来过滤行的，而聚合函数是分组后才会计算的，所以得换个思路，用以下几种方法来复用这些中间结果：

方案1：用CTE（公共表达式）提前算好聚合值 #

CTE可以先把你需要的两个聚合结果一次性计算出来，后面直接引用别名就行，可读性很高：

WITH aggregated_data AS (
    SELECT 
        id,
        SUM(col1) + SUM(col2) AS total_col1_col2,
        SUM(col2) - SUM(col1) AS net_col1_col2,
        col3,
        col4
    FROM data4sim
    WHERE end_time <= :input_time
    GROUP BY id, col3, col4
)
SELECT 
    id,
    net_col1_col2
FROM aggregated_data
WHERE 
    total_col1_col2 > 0
    AND total_col1_col2 <= ABS(COALESCE(col3, 0) - (col4 - net_col1_col2)) + :EPS;

方案2：用子查询封装聚合逻辑 #

和CTE逻辑差不多，把聚合计算放到子查询里，外层直接用别名：

SELECT 
    id,
    net_col1_col2
FROM (
    SELECT 
        id,
        SUM(col1) + SUM(col2) AS total_col1_col2,
        SUM(col2) - SUM(col1) AS net_col1_col2,
        col3,
        col4
    FROM data4sim
    WHERE end_time <= :input_time
    GROUP BY id, col3, col4
) AS sub_query
WHERE 
    total_col1_col2 > 0
    AND total_col1_col2 <= ABS(COALESCE(col3, 0) - (col4 - net_col1_col2)) + :EPS;

方案3：部分数据库支持直接在HAVING里用聚合别名 #

如果你的数据库是PostgreSQL、SQL Server或者MySQL 8.0及以上，可以直接在HAVING子句里用SELECT里定义的聚合别名，省得重复写：

SELECT 
    id,
    SUM(col2) - SUM(col1) AS net_col1_col2,
    SUM(col1) + SUM(col2) AS total_col1_col2
FROM data4sim
WHERE end_time <= :input_time
GROUP BY id, col3, col4
HAVING 
    total_col1_col2 > 0
    AND total_col1_col2 <= ABS(COALESCE(col3, 0) - (col4 - net_col1_col2)) + :EPS;

要注意的点： #

• 别再往WHERE里塞聚合别名了，WHERE管的是分组前的行过滤，这时候聚合结果还没出来呢，根本用不了。
• GROUP BY里必须包含所有非聚合的列（比如col3、col4），不然大部分数据库会报错。

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