多表关联查询时如何获取分组统计列的最大值？

已知表结构 #

• 活动表 event：字段为 name，存储活动唯一名称
• 票务表 ticket：字段为 event_name，关联对应活动的名称

问题与报错说明 #

• 需求：仅输出票务统计数（event_count）最大的活动，格式为活动名|统计数，示例结果为 Sun|2
• 报错触发：在原有统计查询的HAVING子句中，直接将占位符0替换为MAX(COUNT(event_name))时，触发语法错误
• 报错核心原因：SQL 禁止同一查询块内嵌套聚合函数——COUNT(event_name)是分组级聚合（针对每个活动的票务数统计），MAX()需基于「所有活动的统计结果集」再聚合，二者属于不同层级，无法直接嵌套使用

正确实现代码 #

方案1：子查询匹配最大统计值 #

-- 适用于所有支持子查询的SQL数据库
SELECT CONCAT_WS('|', e.name, COUNT(t.event_name)) AS final_result
FROM event e
LEFT JOIN ticket t ON e.name = t.event_name
GROUP BY e.name
-- 匹配等于最大统计数的活动
HAVING COUNT(t.event_name) = (
    -- 子查询先算出所有活动的统计数，再取最大值
    SELECT MAX(cnt)
    FROM (
        SELECT COUNT(event_name) AS cnt
        FROM ticket
        GROUP BY event_name
    ) AS event_stats
);

方案2：窗口函数排序（优先推荐） #

-- 适用于支持窗口函数的数据库（MySQL 8.0+、PostgreSQL、SQL Server等）
SELECT CONCAT_WS('|', event_name, event_count) AS final_result
FROM (
    SELECT
        e.name AS event_name,
        COUNT(t.event_name) AS event_count,
        -- 按统计数降序排名，同值活动排名一致
        DENSE_RANK() OVER (ORDER BY COUNT(t.event_name) DESC) AS rank_num
    FROM event e
    LEFT JOIN ticket t ON e.name = t.event_name
    GROUP BY e.name
) AS ranked_events
-- 仅取排名第一的活动（即统计数最大的）
WHERE rank_num = 1;

输出验证 #

上述代码执行后，会直接返回符合要求的格式结果（如Sun|2），若存在多个活动统计数同为最大值，会返回所有符合条件的结果。

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