Oracle SQL查询中如何新增列实现Vikt与AntalOrder两值相除

你可以通过两种方式实现新增计算列新增，Oracle不支持在同层级SELECT中直接引用别名，以下是可直接运行的实现：

方案1：直接复用聚合表达式（性能最优，写法最简单） #

select 
    O08T1.pickdate,
    O08T1.divcode,
    sum(O08T1.calcwght) as Vikt,
    sum(O08T1.calcvol) as Volym,
    count(O08T1.ordno) as AntalOrder,
    -- 新增第五列，内置除零异常处理
    sum(O08T1.calcwght) / NULLIF(count(O08T1.ordno), 0) as ViktPerOrder
from O08T1  
group by O08T1.pickdate, O08T1.divcode
order by Pickdate DESC

• 用NULLIF(count(O08T1.ordno), 0)处理除数为0的场景，当订单数为0时会自动返回NULL，避免触发除以零报错

方案2：CTE写法（可读性更高） #

如果希望直接引用别名可以用公共表达式实现：

WITH order_agg AS (
    select 
        O08T1.pickdate,
        O08T1.divcode,
        sum(O08T1.calcwght) as Vikt,
        sum(O08T1.calcvol) as Volym,
        count(O08T1.ordno) as AntalOrder
    from O08T1  
    group by O08T1.pickdate, O08T1.divcode
)
SELECT 
    pickdate,
    divcode,
    Vikt,
    Volym,
    AntalOrder,
    Vikt / NULLIF(AntalOrder, 0) as ViktPerOrder
FROM order_agg
order by Pickdate DESC

内容的提问来源于stack exchange，提问作者Filip Skagerström