在MySQL单查询中实现拆分、子串检索与计数求和的组合操作

针对你遇到的无法修改表结构、需要统计字段中带乘数的5位代码出现次数的问题，这里提供两种SQL解决方案，分别适配MySQL 8.0+和5.x版本：

方案1：MySQL 8.0+（支持CTE和正则函数） #

利用递归公共表表达式（CTE）和正则提取函数，将每条记录拆分为独立的代码条目，再聚合统计：

WITH split_codes AS (
    -- 提取记录中第一个5位代码，数量固定为1
    SELECT
        REGEXP_SUBSTR(code_field, '^[0-9]{5}') AS code,
        1 AS quantity
    FROM your_table
    WHERE REGEXP_SUBSTR(code_field, '^[0-9]{5}') IS NOT NULL
    
    UNION ALL
    
    -- 提取分号后的第二个代码及对应乘数（无乘数则默认1）
    SELECT
        REGEXP_SUBSTR(SUBSTRING_INDEX(code_field, '; ', -1), '[0-9]{5}') AS code,
        CASE
            WHEN SUBSTRING_INDEX(code_field, '; ', -1) LIKE '%x %'
            THEN CAST(REGEXP_SUBSTR(SUBSTRING_INDEX(code_field, '; ', -1), '^[0-9]+') AS UNSIGNED)
            ELSE 1
        END AS quantity
    FROM your_table
    WHERE code_field LIKE '%;%'
)
SELECT
    code AS `5-digit Code`,
    SUM(quantity) AS Count
FROM split_codes
WHERE code IS NOT NULL
GROUP BY code
ORDER BY Count DESC;

逻辑说明： #

1. 第一个SELECT块：提取每条记录开头的5位代码，因为第一个代码永远是单次出现，数量设为1。
2. 第二个SELECT块：仅处理包含分号的记录，先拆分出分号后的内容，再从中提取5位代码；同时判断是否存在乘数（通过x 标识），有则提取乘数数值，无则默认数量为1。
3. 最后通过CTE合并所有条目，按代码分组求和得到总次数。

方案2：兼容MySQL 5.x（无CTE和REGEXP_SUBSTR） #

如果你的MySQL版本较低，可使用基础字符串函数实现相同逻辑：

SELECT
    code AS `5-digit Code`,
    SUM(quantity) AS Count
FROM (
    -- 提取第一个5位代码
    SELECT
        LEFT(code_field, 5) AS code,
        1 AS quantity
    FROM your_table
    WHERE LEFT(code_field, 5) REGEXP '^[0-9]{5}'
    
    UNION ALL
    
    -- 提取分号后的第二个代码及乘数
    SELECT
        SUBSTRING(
            SUBSTRING_INDEX(code_field, '; ', -1),
            LOCATE(' ', SUBSTRING_INDEX(code_field, '; ', -1)) + 2,
            5
        ) AS code,
        CASE
            WHEN SUBSTRING_INDEX(code_field, '; ', -1) LIKE '%x %'
            THEN CAST(LEFT(SUBSTRING_INDEX(code_field, '; ', -1), LOCATE('x ', SUBSTRING_INDEX(code_field, '; ', -1)) - 1) AS UNSIGNED)
            ELSE 1
        END AS quantity
    FROM your_table
    WHERE code_field LIKE '%;%'
) AS split_codes
WHERE code REGEXP '^[0-9]{5}'
GROUP BY code
ORDER BY Count DESC;

逻辑说明： #

1. 第一个子查询：通过LEFT函数取记录前5位，并用正则确保是纯数字代码。
2. 第二个子查询：拆分分号后的内容，通过LOCATE定位空格位置来提取5位代码；乘数部分通过判断x 标识，提取对应数字。
3. 最后合并子查询结果，分组求和得到统计数据。

验证结果 #

针对你提供的5条测试数据，两种方案都会输出：

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