SQL如何在单SELECT语句中复用计算列进行除法运算？

可以在单SELECT语句中实现，核心思路是先通过子查询或横向连接预先计算出两个聚合列，再在外部引用这两个列进行除法运算，避免重复编写SUM语句。

方法1：使用子查询 #

将聚合计算逻辑封装在子查询中，外层直接引用子查询生成的列：

SELECT
    CalculatedCol1,
    CalculatedCol2,
    -- 加入NULLIF避免除数为0时抛出错误
    CalculatedCol1 / NULLIF(CalculatedCol2, 0) AS Division
FROM (
    SELECT
        SUM(CASE WHEN (t2.col1 = 'Eligible' AND t2.col2 != 'Exempt') THEN 1 ELSE 0 END) AS CalculatedCol1,
        SUM(CASE WHEN t2.col1 = 'Eligible' AND t2.col2 != 'Exempt' AND (t2.Date1 < t2.Date2) THEN 1 ELSE 0 END) AS CalculatedCol2
    FROM t1
    LEFT JOIN t2 ON t2.fk = t1.pk
    WHERE blah blah blah
) AS agg_result

方法2：使用横向连接（适用于SQL Server/PostgreSQL/MySQL 8.0+） #

通过CROSS APPLY（SQL Server）或LATERAL JOIN（PostgreSQL/MySQL）在同一查询层级中预先计算聚合值，再引用：

-- SQL Server 写法
SELECT
    agg.CalculatedCol1,
    agg.CalculatedCol2,
    agg.CalculatedCol1 / NULLIF(agg.CalculatedCol2, 0) AS Division
FROM t1
LEFT JOIN t2 ON t2.fk = t1.pk
CROSS APPLY (
    SELECT
        SUM(CASE WHEN (t2.col1 = 'Eligible' AND t2.col2 != 'Exempt') THEN 1 ELSE 0 END) AS CalculatedCol1,
        SUM(CASE WHEN t2.col1 = 'Eligible' AND t2.col2 != 'Exempt' AND (t2.Date1 < t2.Date2) THEN 1 ELSE 0 END) AS CalculatedCol2
) AS agg
WHERE blah blah blah
GROUP BY agg.CalculatedCol1, agg.CalculatedCol2

-- PostgreSQL/MySQL 8.0+ 写法
SELECT
    agg.CalculatedCol1,
    agg.CalculatedCol2,
    agg.CalculatedCol1 / NULLIF(agg.CalculatedCol2, 0) AS Division
FROM t1
LEFT JOIN t2 ON t2.fk = t1.pk
LEFT JOIN LATERAL (
    SELECT
        SUM(CASE WHEN (t2.col1 = 'Eligible' AND t2.col2 != 'Exempt') THEN 1 ELSE 0 END) AS CalculatedCol1,
        SUM(CASE WHEN t2.col1 = 'Eligible' AND t2.col2 != 'Exempt' AND (t2.Date1 < t2.Date2) THEN 1 ELSE 0 END) AS CalculatedCol2
) AS agg ON true
WHERE blah blah blah
GROUP BY agg.CalculatedCol1, agg.CalculatedCol2

注意事项 #

• 加入NULLIF(CalculatedCol2, 0)是为了防止除数为0导致的运行时错误，此时除法结果会返回NULL，你可以根据业务需求替换为默认值（比如用COALESCE(CalculatedCol1 / NULLIF(CalculatedCol2, 0), 0)返回0）。
• 如果你的查询包含分组（GROUP BY），只需要将分组条件添加到子查询或横向连接的对应位置即可。

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