SQL如何从CROSS JOIN分组统计结果中获取COUNT(*)最大值对应行

原代码失效核心原因 #

1. 逻辑运算符误用：SQL中判断逻辑与应该用AND而不是&，后者是位运算符，会导致筛选条件不符合预期
2. 语法不支持嵌套聚合：MAX(COUNT(*))属于非法写法，SQL不允许直接嵌套调用聚合函数，同时外层子查询没有为COUNT(*)指定别名，无法直接引用该列

兼容绝大多数SQL方言的实现方案（无需重命名COUNT(*)） #

该方案适配Codecademy常用的SQLite、MySQL等环境，逻辑简单直接：

SELECT month, COUNT(*) 
FROM newspaper
CROSS JOIN months
WHERE start_month <= month AND end_month >= month
GROUP BY month
ORDER BY COUNT(*) DESC
LIMIT 1;

直接对聚合后的COUNT(*)结果降序排序，取第一条就是最大值对应的行，全程不需要修改COUNT(*)的命名。

并列最大值场景的兼容方案 #

如果存在多个月份的订阅数并列第一需要全部取出，可以用支持窗口函数的写法（兼容MySQL 8.0+、PostgreSQL、SQL Server等主流方言）：

SELECT month, COUNT(*)
FROM (
  SELECT 
    month,
    COUNT(*),
    RANK() OVER(ORDER BY COUNT(*) DESC) AS rk
  FROM newspaper
  CROSS JOIN months
  WHERE start_month <= month AND end_month >= month
  GROUP BY month
) t
WHERE rk = 1;

该写法同样不需要修改原有聚合逻辑里的COUNT(*)命名。

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