如何用MySQL查询员工数最多/最少的分组？

方法一：子查询匹配最大/最小计数（兼容多数数据库） #

先统计每个分组的员工数，再通过子查询找出全局的最大和最小员工数，最后筛选出计数等于这两个值的分组：

SELECT `groupName`, emp_count
FROM (
    SELECT `groupName`, COUNT(*) AS emp_count
    FROM Table1
    WHERE ROLE = 'employee'
    GROUP BY `groupName`
) AS group_counts
WHERE emp_count = (
    SELECT MAX(emp_count) 
    FROM (SELECT COUNT(*) AS emp_count FROM Table1 WHERE ROLE='employee' GROUP BY `groupName`) AS counts
)
OR emp_count = (
    SELECT MIN(emp_count) 
    FROM (SELECT COUNT(*) AS emp_count FROM Table1 WHERE ROLE='employee' GROUP BY `groupName`) AS counts
)
ORDER BY emp_count DESC;

方法二：窗口函数简化逻辑（支持窗口函数的数据库，如MySQL 8+、PostgreSQL等） #

利用窗口函数直接计算全局的最大和最小员工数，一步筛选目标分组，代码更简洁高效：

SELECT `groupName`, emp_count
FROM (
    SELECT 
        `groupName`, 
        COUNT(*) AS emp_count,
        MAX(COUNT(*)) OVER () AS max_emp,
        MIN(COUNT(*)) OVER () AS min_emp
    FROM Table1
    WHERE ROLE = 'employee'
    GROUP BY `groupName`
) AS group_counts
WHERE emp_count IN (max_emp, min_emp)
ORDER BY emp_count DESC;

两种方法都能实现需求：如果有多个分组员工数同为最大值或最小值，都会被一并返回（比如示例中的2b、3c两个分组都会出现在结果里）。

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