SQL分组查询问题：无法正确统计交易数最多的年月

首先，咱们先理清你遇到的两个核心问题：

1. 为什么原来的查询会返回重复的年月？ #

你最初用GROUP BY OrderDate，但OrderDate是包含具体日期（甚至时间）的字段，这相当于按每一天分组，而不是按年月。所以2011年6月的30天会生成30行记录，自然会出现重复的年月和零散的orderCount。

2. 为什么修改GROUP BY后报错？ #

你尝试的GROUP BY DATENAME(month, soh.OrderDate), DATENAME(YYYY, soh.OrderDate)有两个小问题：

• DATENAME(YYYY, ...)的参数不对，应该用DATENAME(year, ...)（或者更简洁的YEAR(soh.OrderDate)）；
• SELECT子句里的非聚合列必须和GROUP BY的表达式完全匹配。比如你SELECT里用year(OrderDate)，GROUP BY里就得用year(OrderDate)，而不是DATENAME(year, ...)，否则SQL引擎会认为两者是不同的表达式。

正确的解决方案 #

要实现“找出销售交易数量最多的年月”，我们需要先正确按年月分组统计，再筛选出count最大的记录。这里提供两种常用方法：

方法一：使用窗口函数（推荐，更简洁灵活） #

窗口函数可以直接给每个分组的count排名，然后取排名第一的记录：

WITH MonthlyOrderStats AS (
    SELECT 
        YEAR(OrderDate) AS orderYear,
        DATENAME(month, OrderDate) AS orderMonth,
        COUNT(SalesOrderID) AS orderCount,
        -- 按orderCount降序排名，相同最高count的月份会并列第一
        RANK() OVER (ORDER BY COUNT(SalesOrderID) DESC) AS orderRank
    FROM Sales.SalesOrderHeader
    -- 确保GROUP BY和SELECT的非聚合列完全一致
    GROUP BY YEAR(OrderDate), DATENAME(month, OrderDate)
)
SELECT orderYear, orderMonth, orderCount
FROM MonthlyOrderStats
WHERE orderRank = 1;

方法二：使用子查询筛选最大值 #

如果你的SQL版本不支持窗口函数，可以用嵌套子查询先找出最大的orderCount，再匹配对应的年月：

SELECT 
    YEAR(OrderDate) AS orderYear,
    DATENAME(month, OrderDate) AS orderMonth,
    COUNT(SalesOrderID) AS orderCount
FROM Sales.SalesOrderHeader
GROUP BY YEAR(OrderDate), DATENAME(month, OrderDate)
HAVING COUNT(SalesOrderID) = (
    -- 先算出所有年月的orderCount，再取最大值
    SELECT MAX(monthlyCount)
    FROM (
        SELECT COUNT(SalesOrderID) AS monthlyCount
        FROM Sales.SalesOrderHeader
        GROUP BY YEAR(OrderDate), DATENAME(month, OrderDate)
    ) AS MonthlyCounts
);

额外优化建议 #

如果你需要对年月进行排序，用月份名称（比如"June"）排序会出现字符串排序的问题（比如April会排在January前面）。可以改用当月第一天的日期来分组，这样排序更准确：

WITH MonthlyOrderStats AS (
    SELECT 
        -- 生成当月第一天的日期，方便排序
        DATEFROMPARTS(YEAR(OrderDate), MONTH(OrderDate), 1) AS monthStart,
        COUNT(SalesOrderID) AS orderCount,
        RANK() OVER (ORDER BY COUNT(SalesOrderID) DESC) AS orderRank
    FROM Sales.SalesOrderHeader
    GROUP BY DATEFROMPARTS(YEAR(OrderDate), MONTH(OrderDate), 1)
)
SELECT 
    YEAR(monthStart) AS orderYear,
    DATENAME(month, monthStart) AS orderMonth,
    orderCount
FROM MonthlyOrderStats
WHERE orderRank = 1
ORDER BY monthStart;

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