Oracle中如何按列去重统计并按另一列汇总详情？

这个需求完全可行，核心是按Type和Notes分组，同时将每组内的Subtype与其出现次数拼接汇总为Details列，以下是具体实现方案：

实现思路 #

1. 先对Type、Notes、Subtype三级分组，统计每个子类型的出现次数
2. 将子类型和对应次数拼接为Subtype: N次格式的字符串
3. 再按Type和Notes二次分组，把拼接后的字符串聚合为Details列

不同数据库的代码实现 #

MySQL/MariaDB #

SELECT 
    Type,
    Notes,
    GROUP_CONCAT(DISTINCT CONCAT(Subtype, ': ', cnt) SEPARATOR ', ') AS Details
FROM (
    SELECT 
        Type,
        Notes,
        Subtype,
        COUNT(*) AS cnt
    FROM your_table
    GROUP BY Type, Notes, Subtype
) AS sub_query
GROUP BY Type, Notes;

SQL Server（2017及以上版本） #

SELECT 
    Type,
    Notes,
    STRING_AGG(CONCAT(Subtype, ': ', cnt), ', ') AS Details
FROM (
    SELECT 
        Type,
        Notes,
        Subtype,
        COUNT(*) AS cnt
    FROM your_table
    GROUP BY Type, Notes, Subtype
) AS sub_query
GROUP BY Type, Notes;

PostgreSQL #

SELECT 
    Type,
    Notes,
    STRING_AGG(CONCAT(Subtype, ': ', cnt), ', ') AS Details
FROM (
    SELECT 
        Type,
        Notes,
        Subtype,
        COUNT(*) AS cnt
    FROM your_table
    GROUP BY Type, Notes, Subtype
) AS sub_query
GROUP BY Type, Notes;

Oracle（11gR2及以上版本） #

SELECT 
    Type,
    Notes,
    LISTAGG(CONCAT(Subtype, ': ', cnt), ', ') WITHIN GROUP (ORDER BY Subtype) AS Details
FROM (
    SELECT 
        Type,
        Notes,
        Subtype,
        COUNT(*) AS cnt
    FROM your_table
    GROUP BY Type, Notes, Subtype
) AS sub_query
GROUP BY Type, Notes;

关键说明 #

• 子查询先完成Subtype的次数统计，避免直接在聚合逻辑中重复计算
• 可根据需求调整分隔符（比如把, 换成; ）或子类型的排序顺序（在聚合函数中添加ORDER BY）

内容的提问来源于stack exchange，提问作者Artur Dos Santos