AdventureWorks数据库SQL查询按GroupName分组Name计数最小的所有分组

你可以用RANK()窗口函数一步实现，单条SQL语句如下：

SELECT group_name_count, GroupName
FROM (
    SELECT 
        COUNT(Name) AS group_name_count,
        GroupName,
        RANK() OVER(ORDER BY COUNT(Name) ASC) AS count_rank
    FROM HumanResources.Department
    WHERE GroupName < (SELECT MAX(GroupName) FROM HumanResources.Department)
    GROUP BY GroupName
) AS t
WHERE count_rank = 1

代码说明 #

• 内层查询先按GroupName分组统计每个组的Name数量，同时用RANK()窗口函数给计数值从小到大排序，计数相同的分组会拿到相同的排序值
• 外层筛选排序值为1的记录，也就是所有计数等于最小值的分组
• 保留了你原有存储过程里的GroupName < 最大GroupName的过滤条件，和原业务逻辑保持一致

如果不想用窗口函数，也可以用子查询先取最小计数再匹配：

SELECT COUNT(Name) AS group_name_count, GroupName
FROM HumanResources.Department
WHERE GroupName < (SELECT MAX(GroupName) FROM HumanResources.Department)
GROUP BY GroupName
HAVING COUNT(Name) = (
    SELECT MIN(group_count)
    FROM (
        SELECT COUNT(Name) AS group_count
        FROM HumanResources.Department
        WHERE GroupName < (SELECT MAX(GroupName) FROM HumanResources.Department)
        GROUP BY GroupName
    ) AS t
)

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