如何通过SQL查询员工表中出现次数最多的生日月份？

没问题，我来帮你搞定这个需求！你之前的语句已经能拿到所有员工的生日月份，但要直接找出出现次数最多的月份，我们可以通过分组统计+排序的方式实现，甚至还能处理多个月份人数相同的并列情况。下面分不同数据库给你具体的SQL方案：

SQL Server 方案（匹配你用的DATEPART语法） #

基础版（只返回一个月份，若有并列仅取第一个） #

这个写法简单直接，先按月份分组统计人数，再按人数降序排序，取最前面的结果：

SELECT TOP 1 
    DATEPART(m, dateofbirth) AS birthday_month,
    COUNT(*) AS employee_count
FROM employee
GROUP BY DATEPART(m, dateofbirth)
ORDER BY employee_count DESC;

进阶版（返回所有人数最多的月份，处理并列情况） #

如果存在多个月份生日人数相同且都是最多的，用窗口函数RANK()可以把这些月份都查出来：

WITH MonthCounts AS (
    SELECT 
        DATEPART(m, dateofbirth) AS birthday_month,
        COUNT(*) AS employee_count,
        RANK() OVER (ORDER BY COUNT(*) DESC) AS rank_num
    FROM employee
    GROUP BY DATEPART(m, dateofbirth)
)
SELECT birthday_month, employee_count
FROM MonthCounts
WHERE rank_num = 1;

其他数据库适配方案 #

如果你用的是MySQL或PostgreSQL，语法略有不同，我也整理了对应的写法：

MySQL 版本 #

• 基础版：

SELECT 
    MONTH(dateofbirth) AS birthday_month,
    COUNT(*) AS employee_count
FROM employee
GROUP BY MONTH(dateofbirth)
ORDER BY employee_count DESC
LIMIT 1;

• 进阶版（处理并列）：

WITH MonthCounts AS (
    SELECT 
        MONTH(dateofbirth) AS birthday_month,
        COUNT(*) AS employee_count,
        RANK() OVER (ORDER BY COUNT(*) DESC) AS rank_num
    FROM employee
    GROUP BY MONTH(dateofbirth)
)
SELECT birthday_month, employee_count
FROM MonthCounts
WHERE rank_num = 1;

PostgreSQL 版本 #

• 基础版：

SELECT 
    EXTRACT(MONTH FROM dateofbirth)::INT AS birthday_month,
    COUNT(*) AS employee_count
FROM employee
GROUP BY EXTRACT(MONTH FROM dateofbirth)
ORDER BY employee_count DESC
LIMIT 1;

• 进阶版（处理并列）：

WITH MonthCounts AS (
    SELECT 
        EXTRACT(MONTH FROM dateofbirth)::INT AS birthday_month,
        COUNT(*) AS employee_count,
        RANK() OVER (ORDER BY COUNT(*) DESC) AS rank_num
    FROM employee
    GROUP BY EXTRACT(MONTH FROM dateofbirth)
)
SELECT birthday_month, employee_count
FROM MonthCounts
WHERE rank_num = 1;

简单解释下核心逻辑：

• GROUP BY 按生日月份分组，用COUNT(*)统计每组的员工数量
• ORDER BY employee_count DESC 把人数最多的月份排在最前面
• 进阶版用RANK()窗口函数给每个月份的人数排名，排名为1的就是人数最多的所有月份

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