MySQL报Error Code 1111:Invalid use of group function解决方法

需求说明 #

需要生成包含以下字段的统计报表：

• storeID
• 门店名称
• 在该门店购买过徽章的独立玩家数量
• 未在该门店购买过徽章的独立玩家数量
• 该门店总消费金额
• 玩家在该门店购买过的最贵徽章
• 玩家在该门店购买过的最便宜徽章
• 该门店已售商品的平均价格

报错情况 #

执行原SQL时返回错误：Error Code 1111. Invalid use of group function

原SQL错误原因 #

1. 聚合函数max()、min()不能直接用于WHERE子句，WHERE是分组前的行级过滤，无法直接识别聚合结果
2. 多表关联时未写全关联条件，且缺少GROUP BY分组维度，所有门店维度的统计必须按storeID、storeName分组
3. 未购买玩家统计逻辑错误，原写法的不等值关联会产生大量笛卡尔积，统计结果完全失真
4. 各子查询之间没有写关联条件，默认会产生交叉连接，返回无效数据
5. 语法细节错误：cheapest_badge和avg_spent字段之间漏写逗号；MySQL不存在average()函数，计算平均值需用avg()
6. 最贵/最便宜徽章的统计未按门店分组，会返回全局维度的最值，不符合按门店统计的要求

修正后SQL #

use treasurehunters;

with store_base as (
    -- 先按门店聚合核心统计指标
    select
        s.storeID,
        s.storeName,
        count(distinct p.username) as purchased_user,
        sum(b.cost) as total_spent,
        avg(b.cost) as avg_spent,
        max(b.cost) as max_cost,
        min(b.cost) as min_cost
    from store s
    inner join purchase pur on s.storeID = pur.storeID
    inner join player p on pur.username = p.username
    inner join badge b on pur.badgeID = b.badgeID
    group by s.storeID, s.storeName
),
total_players as (
    -- 统计全平台独立玩家总数
    select count(distinct username) as total_user from player
),
store_badge_max as (
    -- 关联取每个门店最贵的徽章名称
    select
        pur.storeID,
        group_concat(distinct b.badgename separator '、') as expensive_badge
    from purchase pur
    inner join badge b on pur.badgeID = b.badgeID
    inner join store_base sb on pur.storeID = sb.storeID and b.cost = sb.max_cost
    group by pur.storeID
),
store_badge_min as (
    -- 关联取每个门店最便宜的徽章名称
    select
        pur.storeID,
        group_concat(distinct b.badgename separator '、') as cheapest_badge
    from purchase pur
    inner join badge b on pur.badgeID = b.badgeID
    inner join store_base sb on pur.storeID = sb.storeID and b.cost = sb.min_cost
    group by pur.storeID
)
select
    sb.storeID,
    sb.storeName,
    sb.purchased_user as `在该门店购买过徽章的独立玩家数量`,
    (tp.total_user - sb.purchased_user) as `未在该门店购买过徽章的独立玩家数量`,
    sb.total_spent as `该门店总消费金额`,
    sbm.expensive_badge as `玩家在该门店购买过的最贵徽章`,
    sbmin.cheapest_badge as `玩家在该门店购买过的最便宜徽章`,
    sb.avg_spent as `该门店已售商品的平均价格`
from store_base sb
cross join total_players tp
inner join store_badge_max sbm on sb.storeID = sbm.storeID
inner join store_badge_min sbmin on sb.storeID = sbmin.storeID;

说明：如果同个门店存在多个价格相同的最贵/最便宜徽章，上述SQL会用顿号拼接所有符合条件的徽章名；如果只需要返回单个徽章，可将group_concat替换为min(b.badgename)即可。

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