SQL分组函数嵌套过深问题：查询订单量最多的月份及订单数

问题背景 #

现有ORDERS表，包含ORDER_ID、ORDER_DATE字段，需要查询订单量最多的月份及其订单总数，输出格式要求为MONTH（示例：Dec 20）和TOTAL_ORDERS（示例：17）。

尝试的SQL及问题分析 #

第一个SQL语句（报错） #

SELECT 
    TO_CHAR(ORDER_DATE, 'Mon-YY') MONTH, COUNT(ORDER_ID) TOTAL
FROM 
    ORDERS
GROUP BY 
    TO_CHAR(ORDER_DATE, 'Mon-YY')
HAVING 
    MAX(COUNT(ORDER_ID))

执行报错：

Group function nested too deeply in line 4

错误原因：HAVING子句不允许嵌套聚合函数，MAX(COUNT(...))属于非法写法——COUNT是分组后的聚合结果，不能直接用MAX包裹作为HAVING的条件。

第二个SQL语句（跨数据库不兼容） #

SELECT 
    TO_CHAR(ORDER_DATE, 'Mon-YY') MONTH, COUNT(ORDER_ID) TOTAL
FROM 
    ORDERS
GROUP BY 
    TO_CHAR(ORDER_DATE, 'Mon-YY')
HAVING 
    COUNT(ORDER_ID) = (SELECT MAX(COUNT(ORDER_ID))
                       FROM ORDERS 
                       GROUP BY TO_CHAR(ORDER_DATE, 'Mon-YY'));

问题：部分数据库（如MySQL）不支持在子查询中直接使用MAX(COUNT(ORDER_ID))这种嵌套聚合的写法，导致无法在多数据库环境下通用。

跨数据库兼容解决方案 #

方案1：使用窗口函数（主流数据库通用） #

利用RANK()窗口函数标记订单量排名，兼容PostgreSQL、MySQL 8.0+、Oracle、SQL Server等主流数据库：

SELECT MONTH, TOTAL_ORDERS
FROM (
    SELECT 
        TO_CHAR(ORDER_DATE, 'Mon-YY') MONTH,
        COUNT(ORDER_ID) TOTAL_ORDERS,
        RANK() OVER(ORDER BY COUNT(ORDER_ID) DESC) AS rank_num
    FROM ORDERS
    GROUP BY TO_CHAR(ORDER_DATE, 'Mon-YY')
) ranked_data
WHERE rank_num = 1;

• 若存在多个月份订单量并列最高，RANK()会返回所有并列的月份；如果只需要返回任意一个（即使有并列），可将RANK()替换为ROW_NUMBER()。
• 注意：日期格式化函数需根据数据库调整：
  • MySQL：替换TO_CHAR(ORDER_DATE, 'Mon-YY')为DATE_FORMAT(ORDER_DATE, '%b %y')
  • SQL Server：替换为FORMAT(ORDER_DATE, 'MMM yy')

方案2：嵌套子查询（兼容低版本数据库） #

如果需要兼容不支持窗口函数的数据库（如MySQL 5.x），可通过两层子查询先计算各月订单数，再取最大值匹配：

SELECT 
    TO_CHAR(ORDER_DATE, 'Mon-YY') MONTH,
    COUNT(ORDER_ID) TOTAL_ORDERS
FROM ORDERS
GROUP BY TO_CHAR(ORDER_DATE, 'Mon-YY')
HAVING COUNT(ORDER_ID) = (
    SELECT MAX(monthly_total)
    FROM (
        SELECT COUNT(ORDER_ID) AS monthly_total
        FROM ORDERS
        GROUP BY TO_CHAR(ORDER_DATE, 'Mon-YY')
    ) monthly_data
);

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