SQL中能否按列的起始字符分组？附数据表与预期结果示例

问题说明 #

现有数据表：

Name    Value
A          10
ABC         5
A           8
ABC         3
AB          2

要实现的不是精确匹配Name分组，而是按Name的起始字符前缀匹配分组求和，最终得到：

Name    Value
A          28    
AB         10    
ABC         8

简单说就是：每个分组项（比如A、AB）要把所有Name以它开头的记录的Value加起来。

能不能实现？当然可以 #

用SQL就能搞定，核心是通过自关联做前缀匹配，再分组求和。下面拿MySQL举例说明：

实现代码 #

假设原表名为test_table，执行以下SQL：

SELECT 
    t1.Name, 
    SUM(t2.Value) AS Value
FROM 
    (SELECT DISTINCT Name FROM test_table) t1
JOIN 
    test_table t2 ON t2.Name LIKE CONCAT(t1.Name, '%')
GROUP BY 
    t1.Name
ORDER BY 
    t1.Name;

代码拆解 #

• 先通过SELECT DISTINCT Name FROM test_table拿到所有唯一的Name值，作为分组基准（避免原表有重复Name时重复计算）
• 自关联原表t2，用LIKE CONCAT(t1.Name, '%')筛选出所有Name以t1.Name为前缀的记录
• 按t1.Name分组，把匹配到的t2.Value全部求和
• 最后按Name排序，结果就和期望完全一致

不同数据库的适配调整 #

如果使用PostgreSQL或Oracle，字符串拼接方式略有不同：

• PostgreSQL：用||替代CONCAT，即t2.Name LIKE t1.Name || '%'
• Oracle：同样支持||，也可以使用CONCAT函数，按需调整即可

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