MySQL技术求助：新增分销商变更历史统计列及SQL实现

原始数据源 #

SKU ID    Distributor
1        A
2        A
2        B
3        A
3        B
3        C

期望输出 #

SKU ID  Distributor       Distributor Change History
1         A                     1
2         A,B                   2
3         A,B,C                 3

不同数据库的实现代码 #

MySQL/MariaDB 版本

使用GROUP_CONCAT实现分销商字符串合并，结合COUNT统计数量：

SELECT 
    `SKU ID`,
    GROUP_CONCAT(Distributor ORDER BY Distributor SEPARATOR ',') AS Distributor,
    COUNT(DISTINCT Distributor) AS `Distributor Change History`
FROM 
    your_table_name -- 替换为你的实际表名
GROUP BY 
    `SKU ID`
ORDER BY 
    `SKU ID`;

注：若同SKU下不会出现重复的分销商记录，COUNT(DISTINCT Distributor)可替换为COUNT(*)，提升查询效率

PostgreSQL 版本

使用STRING_AGG完成字符串聚合：

SELECT 
    "SKU ID",
    STRING_AGG(Distributor, ',' ORDER BY Distributor) AS Distributor,
    COUNT(DISTINCT Distributor) AS "Distributor Change History"
FROM 
    your_table_name -- 替换为你的实际表名
GROUP BY 
    "SKU ID"
ORDER BY 
    "SKU ID";

SQL Server 2017+ 版本

通过STRING_AGG配合WITHIN GROUP实现有序合并：

SELECT 
    [SKU ID],
    STRING_AGG(Distributor, ',') WITHIN GROUP (ORDER BY Distributor) AS Distributor,
    COUNT(DISTINCT Distributor) AS [Distributor Change History]
FROM 
    your_table_name -- 替换为你的实际表名
GROUP BY 
    [SKU ID]
ORDER BY 
    [SKU ID];

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