如何用SQL查询每年已结束项目数量最多的对应月份

实现思路 #

这里提供两种常用实现方案，你可以根据自己使用的数据库版本选择：

• 方案1：窗口函数实现（推荐，兼容MySQL 8.0+、PostgreSQL、Oracle等主流新版数据库）
  你现有的SQL已经统计出了每年每月的项目数量，只需要在此基础上对同一年的所有月份按项目数倒序排名，过滤出排名为1的记录即可。如果存在同年多个月份项目数相同且均为最高的情况，用RANK()会返回所有并列第一的月份，用ROW_NUMBER()只会随机返回其中一条。

WITH monthly_stats AS (
    SELECT  
        COUNT(proj.projno) nr_proj, 
        EXTRACT(MONTH FROM proj.end_date) AS month,
        EXTRACT(YEAR FROM proj.end_date) AS year
    FROM PROJ
    GROUP BY EXTRACT(MONTH FROM proj.end_date), EXTRACT(YEAR FROM proj.end_date)
),
ranked_stats AS (
    SELECT 
        nr_proj, 
        month, 
        year,
        RANK() OVER (PARTITION BY year ORDER BY nr_proj DESC) AS proj_rank
    FROM monthly_stats
)
SELECT nr_proj, month, year
FROM ranked_stats
WHERE proj_rank = 1;

• 方案2：兼容无窗口函数的老版本数据库
  先统计每年的最高项目数，再把月度统计结果和年度最大值做关联，匹配上的就是对应年份的最高记录：

SELECT m.nr_proj, m.month, m.year
FROM (
    SELECT  
        COUNT(proj.projno) nr_proj, 
        EXTRACT(MONTH FROM proj.end_date) AS month,
        EXTRACT(YEAR FROM proj.end_date) AS year
    FROM PROJ
    GROUP BY EXTRACT(MONTH FROM proj.end_date), EXTRACT(YEAR FROM proj.end_date)
) m
INNER JOIN (
    SELECT year, MAX(nr_proj) AS max_proj
    FROM (
        SELECT  
            COUNT(proj.projno) nr_proj,
            EXTRACT(YEAR FROM proj.end_date) AS year
        FROM PROJ
        GROUP BY EXTRACT(MONTH FROM proj.end_date), EXTRACT(YEAR FROM proj.end_date)
    ) t
    GROUP BY year
) y ON m.year = y.year AND m.nr_proj = y.max_proj;

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