SQL查询学生人数相同分组及人数最少分组的实现方法

当前使用的两张数据库表定义如下：

TABLE students
(
    STUDENT_ID smallint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    GROUP_ID smallint
);

TABLE groups
(
    GROUP_ID smallint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    GROUP_NAME char(5)
);

用于统计各分组学生人数的SQL语句：

SELECT 
    groups.group_name, COUNT (*)
FROM
    students
JOIN 
    groups ON students.group_id = groups.group_id 
GROUP BY 
    groups.group_name;

该查询返回结果如下：

1. 查询学生人数相同的分组 #

实现逻辑：先统计每个分组的学生人数，筛选出被2个及以上分组共用的人数值，最后返回对应这些人数的所有分组即可。

WITH group_cnt AS (
    SELECT 
        group_name,
        COUNT(*) AS student_num
    FROM students s
    JOIN groups g ON s.group_id = g.group_id
    GROUP BY group_name
)
SELECT
    CONCAT('"', group_name, '"') AS group_name,
    student_num AS "COUNT(*)"
FROM group_cnt
WHERE student_num IN (
    SELECT student_num
    FROM group_cnt
    GROUP BY student_num
    HAVING COUNT(*) >= 2
)
ORDER BY student_num, group_name;

返回结果和预期完全一致：

2. 查询学生人数最少的分组 #

实现逻辑：先统计每个分组的学生人数，找到人数的最小值，再返回人数等于该最小值的所有分组即可。

WITH group_cnt AS (
    SELECT 
        group_name,
        COUNT(*) AS student_num
    FROM students s
    JOIN groups g ON s.group_id = g.group_id
    GROUP BY group_name
)
SELECT
    group_name,
    student_num AS "COUNT(*)"
FROM group_cnt
WHERE student_num = (SELECT MIN(student_num) FROM group_cnt)
ORDER BY group_name;

返回结果和预期完全一致：

说明：以上写法使用了CTE公共表表达式，兼容PostgreSQL、MySQL 8.0及以上、SQL Server等主流数据库，如果使用不支持CTE的旧版本，可以将CTE部分替换为相同逻辑的子查询重复执行。

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