MySQL计算查询中如何复用别名？引用NumDrums报#42S22错误

SQL的执行顺序决定了同层级SELECT子句中定义的列别名，无法被同层SELECT的其他计算列直接引用。COUNT()属于聚合函数，这类函数生成的别名在同层引用时，绝大多数数据库都会直接抛出组函数引用错误。原SQL中写的(SELECT NumDrums)是无效标量子查询，既无关联数据源也无法读取同层定义的别名，直接触发42S22报错。

方案1：直接复用聚合逻辑（全版本兼容） #

不需要强行引用别名，直接在UnitTotal计算处重复写COUNT(*)即可，写法最简单，兼容所有SQL数据库：

Select
    customers.CustCompanyName,
    products.ProdUnNum,
    customers.CustGenerator,
    products.ProdID,
    products.ProdSize,
    products.ProdUnitName,
    COUNT(*) as NumDrums,
    COUNT(*) * products.ProdSize as UnitTotal
From
    drums Inner Join
    orders On drums.OrderID = orders.OrderID Inner Join
    manifests On orders.ManifestID = manifests.ManifestID Inner Join
    customers On drums.CustID = customers.CustID
            And orders.CustID = customers.CustID Inner Join
    products On drums.ProdID = products.ProdID
Group By
    customers.CustCompanyName,
    products.ProdUnNum,
    customers.CustGenerator,
    products.ProdID,
    products.ProdSize,
    products.ProdUnitName
Order by
customers.CustCompanyName

提示：原SQL的GROUP BY只包含2个字段，不符合标准SQL的GROUP BY语法要求，在非宽松模式的数据库中会直接报错，上述代码已经补全了所有非聚合列到GROUP BY子句中，保证结果准确。

方案2：嵌套查询实现别名复用（适合复杂聚合场景） #

如果聚合逻辑复杂、不想重复书写计算逻辑，可以先通过子查询/CTE完成聚合生成NumDrums别名，在外层查询直接引用别名计算UnitTotal：

-- CTE写法，支持MySQL8.0+、PostgreSQL、SQL Server等主流新数据库版本
WITH agg_result AS (
    Select
        customers.CustCompanyName,
        products.ProdUnNum,
        customers.CustGenerator,
        products.ProdID,
        products.ProdSize,
        products.ProdUnitName,
        COUNT(*) as NumDrums
    From
        drums Inner Join
        orders On drums.OrderID = orders.OrderID Inner Join
        manifests On orders.ManifestID = manifests.ManifestID Inner Join
        customers On drums.CustID = customers.CustID
                And orders.CustID = customers.CustID Inner Join
        products On drums.ProdID = products.ProdID
    Group By
        customers.CustCompanyName,
        products.ProdUnNum,
        customers.CustGenerator,
        products.ProdID,
        products.ProdSize,
        products.ProdUnitName
)
SELECT 
    *,
    NumDrums * ProdSize AS UnitTotal
FROM agg_result
ORDER BY CustCompanyName

如果使用不支持CTE的旧版数据库（比如MySQL5.x），直接将CTE部分替换为FROM后的嵌套子查询即可，逻辑完全一致。

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