T-SQL查询每类下条目最多的子类别及HAVING子句报错问题

你遇到的HAVING子句报错是SQL标准的执行规则限制导致的：
SQL的执行顺序中，GROUP BY分组操作优先级高于HAVING过滤。当你仅按category字段分组后，每个分组仅保留分组字段category、以及基于分组计算的聚合函数结果，itemCount既没有出现在GROUP BY的分组字段列表中，也没有被聚合函数包裹，数据库无法确定你要取该分组下的哪一个itemCount值来和MAX(itemCount)做比对，因此触发了“字段未包含在聚合函数或GROUP BY子句中”的报错。

使用T-SQL内置的窗口函数可以非常简洁的实现需求，不需要额外的多表关联：

WITH pool AS (
    SELECT
        category,
        subcategory,
        COUNT(item) AS itemCount,
        -- 按category分区，每个分区内按itemCount降序排序
        -- 若需要保留同分类下Item数并列第一的所有子分类，将ROW_NUMBER替换为RANK即可
        ROW_NUMBER() OVER(PARTITION BY category ORDER BY COUNT(item) DESC) AS rn
    FROM [你的实际表名]
    GROUP BY category, subcategory
)
SELECT category, subcategory
FROM pool
WHERE rn = 1

该方案执行效率远高于子查询关联的写法，且逻辑清晰易维护。如果需要指定同分情况下的取值优先级，可以在ORDER BY COUNT(item) DESC后追加排序规则，比如ORDER BY COUNT(item) DESC, subcategory ASC表示同分情况下按子分类名称升序取第一个。

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