MySQL报错ERROR 1111(HY000)：聚合函数使用不当求解决

问题场景 #

执行以下SQL语句时触发错误ERROR 1111 (HY000): Invalid use of group function：

select a2.Email_account, o2.Numero_ordine, totale_ordine2.totale as totale_ordine, totale_ordine1.totale as totale_riferimento
from
    account a1, account a2, ordine o1, ordine o2,
    (
        select o.Numero_ordine as ordine,  SUM(b.Prezzo * b.Quantita) as totale
        from biglietto b, ordine o
        where
            b.Numero_ordine = o.Numero_ordine
        group by b.Numero_ordine
    ) totale_ordine1,
    (
        select o.Numero_ordine as ordine,  SUM(b.Prezzo * b.Quantita) as totale from biglietto b, ordine o
        where
            b.Numero_ordine = o.Numero_ordine
        group by b.Numero_ordine
    ) totale_ordine2
where 
    a1.Email_account ='Vita.Devlen@gmail.com'
    and a1.Email_account = o1.Email_account
    and o2.Email_account = a2.Email_account
    and o1.Numero_ordine = totale_ordine1.ordine
    and o2.Numero_ordine = totale_ordine2.ordine
    and totale_ordine2.totale > max(totale_ordine1.totale);

错误原因 #

MAX()这类聚合函数不能直接放在WHERE子句中，因为WHERE是分组前筛选行的逻辑，聚合函数是分组后计算的结果，二者逻辑阶段不兼容。此外查询里重复定义了两个完全一致的子查询totale_ordine1和totale_ordine2，属于冗余写法。

修正后的SQL #

SELECT 
    a2.Email_account, 
    o2.Numero_ordine, 
    totale_ordine.totale as totale_ordine, 
    ref_totale.max_totale as totale_riferimento
FROM 
    account a1
    JOIN ordine o1 ON a1.Email_account = o1.Email_account
    JOIN (
        SELECT 
            o.Numero_ordine as ordine,  
            SUM(b.Prezzo * b.Quantita) as totale
        FROM biglietto b
        JOIN ordine o ON b.Numero_ordine = o.Numero_ordine
        GROUP BY o.Numero_ordine
    ) totale_ordine1 ON o1.Numero_ordine = totale_ordine1.ordine
    -- 计算目标用户的订单总金额最大值
    JOIN (
        SELECT MAX(totale) as max_totale
        FROM (
            SELECT SUM(b.Prezzo * b.Quantita) as totale
            FROM biglietto b
            JOIN ordine o ON b.Numero_ordine = o.Numero_ordine
            JOIN account a ON o.Email_account = a.Email_account
            WHERE a.Email_account = 'Vita.Devlen@gmail.com'
        ) user_totals
    ) ref_totale
    -- 关联所有订单的总金额（包含用户邮箱）
    JOIN (
        SELECT 
            o.Numero_ordine as ordine, 
            o.Email_account,
            SUM(b.Prezzo * b.Quantita) as totale
        FROM biglietto b
        JOIN ordine o ON b.Numero_ordine = o.Numero_ordine
        GROUP BY o.Numero_ordine, o.Email_account
    ) totale_ordine ON totale_ordine.totale > ref_totale.max_totale
    JOIN account a2 ON totale_ordine.Email_account = a2.Email_account
    JOIN ordine o2 ON totale_ordine.ordine = o2.Numero_ordine;

关键修改说明 #

• 替换旧式连接语法：把原来的逗号分隔表的写法换成JOIN ... ON，逻辑更清晰，避免隐式笛卡尔积问题。
• 单独计算目标用户的最大订单金额：用嵌套子查询先算出Vita.Devlen@gmail.com的所有订单总金额的最大值，再用这个值作为筛选条件。
• 合并冗余子查询：去掉重复的totale_ordine1和totale_ordine2，按需定义子查询，减少冗余计算。
• 调整聚合筛选逻辑：通过预先计算最大金额，在连接阶段筛选出总金额大于该值的订单，避免在WHERE中直接使用聚合函数。

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