SQL多分组聚合问题：如何将按字段前两位分组的结果合并映射为自定义用户组并求和

问题背景 #

我有一个示例数据集（仅用于演示），结构及数据如下：

我的需求是：

• 先按Number字段的前两位字符分组，计算Duration的总和，得到11、22、33、44、55这些分组的求和结果；
• 进一步合并分组：将11和22合并为John组，33和44合并为Mike组，55单独作为Ann组，最终得到如下格式的结果：

我目前写出的初步SQL语句为：

select left(Number,2) as User, sum(Duration) as Total from user_data group by left(Number,2);

但我不知道如何进一步实现分组的合并与映射，恳请专业人士提供解决方案。

解决方案 #

没问题，你可以通过两种实用的方式来实现这个分组合并的需求，根据你的场景选择即可：

方法1：用CASE WHEN直接映射（适合固定少量分组） #

这种方式简单直接，不需要额外建表，直接在查询里通过CASE语句把前两位的分组映射到对应的用户名，再按映射后的名称分组求和：

SELECT
    CASE
        WHEN LEFT(Number, 2) IN ('11', '22') THEN 'John'
        WHEN LEFT(Number, 2) IN ('33', '44') THEN 'Mike'
        WHEN LEFT(Number, 2) = '55' THEN 'Ann'
        -- 如果有未匹配的分组，可以加ELSE分支，比如ELSE '未分组'
    END AS User,
    SUM(Duration) AS Total
FROM user_data
GROUP BY
    CASE
        WHEN LEFT(Number, 2) IN ('11', '22') THEN 'John'
        WHEN LEFT(Number, 2) IN ('33', '44') THEN 'Mike'
        WHEN LEFT(Number, 2) = '55' THEN 'Ann'
    END;

如果你不想重复写CASE逻辑，也可以先做子查询生成映射后的分组，再外层求和：

SELECT
    user_group AS User,
    SUM(Duration) AS Total
FROM (
    SELECT
        Duration,
        CASE
            WHEN LEFT(Number, 2) IN ('11', '22') THEN 'John'
            WHEN LEFT(Number, 2) IN ('33', '44') THEN 'Mike'
            WHEN LEFT(Number, 2) = '55' THEN 'Ann'
        END AS user_group
    FROM user_data
) AS grouped_data
GROUP BY user_group;

方法2：用映射表JOIN（适合分组规则需维护的场景） #

如果后续分组映射规则可能变化，或者分组数量较多，建议创建一个映射表，通过JOIN关联计算，这样修改规则时只需要更新表数据，不用改SQL：

首先创建并填充映射表：

CREATE TABLE user_group_mapping (
    prefix VARCHAR(2) PRIMARY KEY,
    user_name VARCHAR(20) NOT NULL
);

INSERT INTO user_group_mapping (prefix, user_name)
VALUES
    ('11', 'John'),
    ('22', 'John'),
    ('33', 'Mike'),
    ('44', 'Mike'),
    ('55', 'Ann');

然后执行查询：

SELECT
    ugm.user_name AS User,
    SUM(ud.Duration) AS Total
FROM user_data ud
JOIN user_group_mapping ugm ON LEFT(ud.Number, 2) = ugm.prefix
GROUP BY ugm.user_name;

两种方法最终都会得到你想要的结果：

• John组总和：65+34+56+34+56+78+45 = 368
• Mike组总和：34+67+23+67+44 = 201
• Ann组总和：34

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