求MySQL查询语句：遍历表中双层数组并求和所有支付金额

解决方案 #

方案1：递归CTE（MySQL 8.0+适用）

递归CTE可以自动拆分&分隔的所有支付项，提取每个项的金额后求和，无需额外建表：

WITH RECURSIVE payment_split AS (
    -- 初始行：拆分第一个支付项，保留剩余未拆分的字符串
    SELECT
        SUBSTRING_INDEX(payments, '&', 1) AS single_payment,
        TRIM(SUBSTRING(payments, LENGTH(SUBSTRING_INDEX(payments, '&', 1)) + 2)) AS rest_payments
    FROM your_table
    WHERE payments IS NOT NULL AND payments != ''
    
    UNION ALL
    
    -- 递归拆分剩余字符串
    SELECT
        SUBSTRING_INDEX(rest_payments, '&', 1) AS single_payment,
        TRIM(SUBSTRING(rest_payments, LENGTH(SUBSTRING_INDEX(rest_payments, '&', 1)) + 2)) AS rest_payments
    FROM payment_split
    WHERE rest_payments IS NOT NULL AND rest_payments != ''
)
SELECT
    t.id, -- 替换成你的表主键/唯一标识列
    SUM(CAST(SUBSTRING_INDEX(single_payment, '/', 1) AS DECIMAL(10,2))) AS total_paid
FROM your_table t
JOIN payment_split ps ON LOCATE(CONCAT('&', ps.single_payment, '&'), CONCAT('&', t.payments, '&')) > 0
GROUP BY t.id;

方案2：数字辅助表（兼容MySQL 5.x）

如果你的MySQL版本不支持递归CTE，可以先建一个数字表（用来对应支付项的位置）：

1. 创建数字表（插入足够多的数字，覆盖你最多的支付项数量）：

CREATE TABLE nums (n INT UNSIGNED NOT NULL PRIMARY KEY);
INSERT INTO nums VALUES (1),(2),(3),(4),(5),...,(50); -- 按需增加

2. 用数字表拆分字符串并求和：

SELECT
    id, -- 替换成你的表主键/唯一标识列
    SUM(
        CAST(
            SUBSTRING_INDEX(SUBSTRING_INDEX(payments, '&', n), '&', -1)
            AS DECIMAL(10,2)
        )
    ) AS total_paid
FROM your_table
JOIN nums ON n <= 1 + LENGTH(payments) - LENGTH(REPLACE(payments, '&', ''))
WHERE payments IS NOT NULL AND payments != ''
GROUP BY id;

核心要点 #

• 用SUBSTRING_INDEX(single_payment, '/', 1)提取每个支付项的金额部分
• 必须用CAST(...) AS DECIMAL(10,2)把字符串金额转成数值类型，否则求和会变成字符串拼接
• 递归CTE更灵活，适合新版本MySQL；数字表方案兼容性强，适合老版本

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