MySQL中如何统计各部门唯一年龄数并合并显示唯一年龄列表？

假设你当前获取前两列的查询类似如下：

SELECT 
    department,
    COUNT(DISTINCT age) AS unique_age_count
FROM students
GROUP BY department;

要添加第三列展示该部门的所有唯一年龄，不同数据库的实现方式略有差异，以下是主流数据库的解决方案：

MySQL（5.7及以上版本） #

使用GROUP_CONCAT函数聚合去重后的年龄，支持排序和自定义分隔符：

SELECT 
    department,
    COUNT(DISTINCT age) AS unique_age_count,
    GROUP_CONCAT(DISTINCT age ORDER BY age SEPARATOR ', ') AS unique_ages
FROM students
GROUP BY department;

• DISTINCT：确保聚合的年龄不重复
• ORDER BY age：让年龄按顺序排列
• SEPARATOR ', '：指定年龄之间的分隔符，可根据需求修改

PostgreSQL #

使用STRING_AGG函数，需将数值类型的age转换为文本类型：

SELECT 
    department,
    COUNT(DISTINCT age) AS unique_age_count,
    STRING_AGG(DISTINCT age::TEXT, ', ' ORDER BY age) AS unique_ages
FROM students
GROUP BY department;

SQL Server（2017及以上版本） #

使用STRING_AGG函数，通过WITHIN GROUP指定排序规则：

SELECT 
    department,
    COUNT(DISTINCT age) AS unique_age_count,
    STRING_AGG(DISTINCT age, ', ') WITHIN GROUP (ORDER BY age) AS unique_ages
FROM students
GROUP BY department;

Oracle #

19c及以上版本 #

直接使用支持DISTINCT的LISTAGG函数：

SELECT 
    department,
    COUNT(DISTINCT age) AS unique_age_count,
    LISTAGG(DISTINCT age, ', ') WITHIN GROUP (ORDER BY age) AS unique_ages
FROM students
GROUP BY department;

18c及以下版本 #

由于旧版本LISTAGG不支持DISTINCT，需先通过子查询去重：

SELECT 
    department,
    COUNT(age) AS unique_age_count,
    LISTAGG(age, ', ') WITHIN GROUP (ORDER BY age) AS unique_ages
FROM (
    SELECT DISTINCT department, age FROM students
) t
GROUP BY department;

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