如何用单条MySQL查询统计Code字段前缀的重复出现次数

原SQL存在几个关键问题：

• 分组逻辑错误：按IDs分组无法统计Code前缀的重复次数，应该按处理后的Code前缀分组
• 语法错误：LEFT函数参数错误（多了一个多余的%），CONCAT和CASE WHEN的括号未正确闭合
• 聚合方式错误：用COUNT包裹CASE WHEN返回的1/0值，无法正确计算同前缀的记录数

正确解法1：使用窗口函数（推荐，简洁高效） #

窗口函数可以直接在每条记录上计算对应Code前缀的重复次数，无需分组后再关联：

SELECT 
    IDs,
    Code,
    COUNT(*) OVER (PARTITION BY LEFT(Code, CHAR_LENGTH(Code) - 3)) AS samecode
FROM tbl;

• LEFT(Code, CHAR_LENGTH(Code)-3)：提取Code字段去掉最后3个字符的前缀部分
• PARTITION BY：将所有前缀相同的记录划分为同一组
• COUNT(*) OVER (...)：计算当前组内的总记录数，作为每条记录的samecode值

正确解法2：子查询关联（兼容不支持窗口函数的数据库） #

如果你的数据库不支持窗口函数，可以先统计每个前缀的重复次数，再关联回原表：

SELECT 
    t.IDs,
    t.Code,
    s.samecode
FROM tbl t
INNER JOIN (
    SELECT 
        LEFT(Code, CHAR_LENGTH(Code) - 3) AS code_prefix,
        COUNT(*) AS samecode
    FROM tbl
    GROUP BY code_prefix
) s ON LEFT(t.Code, CHAR_LENGTH(t.Code) - 3) = s.code_prefix;

• 子查询先统计每个Code前缀的出现次数
• 通过前缀字段将原表和子查询结果关联，为每条记录匹配对应的重复次数

验证结果 #

执行上述任意语句后，都会得到你期望的结果：

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