如何实现数据库多值单元格按位置取对应值相乘后求和

• 先将Quantity和Price列的逗号分隔字符串拆分为多行，拆分过程中为每个值生成从1开始计数的位置索引，保证两列同位置的数值索引一致
• 按原行唯一标识（如示例中的Customer_Number+Book_ID组合）和位置索引关联，匹配同位置的数量值和单价
• 对每对匹配的数值做乘法运算，所有乘积相加得到最终Total值

MySQL 8.0+ #

利用JSON_TABLE将逗号分隔的字符串转换为带序号的结构化数据，关联同序号的值计算求和：

WITH sample_data AS (
    -- 此处替换为你的实际业务表查询逻辑
    SELECT '2, 3, 5' AS Quantity, '109.20, 72.80, 59.15' AS Price
)
SELECT SUM(qty * price) AS calculated_total
FROM sample_data,
     JSON_TABLE(
         CONCAT('["', REPLACE(REPLACE(Quantity, ' ', ''), ',', '","'), '"]'),
         '$[*]' COLUMNS (
             idx FOR ORDINALITY,
             qty DECIMAL(10,2) PATH '$'
         )
     ) AS qty_split,
     JSON_TABLE(
         CONCAT('["', REPLACE(REPLACE(Price, ' ', ''), ',', '","'), '"]'),
         '$[*]' COLUMNS (
             idx FOR ORDINALITY,
             price DECIMAL(10,2) PATH '$'
         )
     ) AS price_split
WHERE qty_split.idx = price_split.idx;

运行结果为732.55，和预期一致。

PostgreSQL #

用STRING_TO_ARRAY将字符串转成数组后，通过UNNEST拆分为带序号的行再关联计算：

WITH sample_data AS (
    -- 此处替换为你的实际业务表查询逻辑
    SELECT '2, 3, 5' AS Quantity, '109.20, 72.80, 59.15' AS Price
)
SELECT SUM(qty::numeric * price::numeric) AS calculated_total
FROM sample_data,
     UNNEST(STRING_TO_ARRAY(REPLACE(Quantity, ' ', ''), ',')) WITH ORDINALITY AS q(qty, idx),
     UNNEST(STRING_TO_ARRAY(REPLACE(Price, ' ', ''), ',')) WITH ORDINALITY AS p(price, idx)
WHERE q.idx = p.idx;

SQL Server 2022+ #

使用带ordinal参数的STRING_SPLIT拆分字符串，关联同序号的值计算：

WITH sample_data AS (
    -- 此处替换为你的实际业务表查询逻辑
    SELECT '2, 3, 5' AS Quantity, '109.20, 72.80, 59.15' AS Price
)
SELECT SUM(CAST(q.value AS DECIMAL(10,2)) * CAST(p.value AS DECIMAL(10,2))) AS calculated_total
FROM sample_data
CROSS APPLY STRING_SPLIT(REPLACE(Quantity, ' ', ''), ',', 1) q
CROSS APPLY STRING_SPLIT(REPLACE(Price, ' ', ''), ',', 1) p
WHERE q.ordinal = p.ordinal;

如果需要将计算结果写入原表的Total字段，将上述查询的求和结果作为更新值，匹配原表的唯一行标识执行UPDATE操作即可。低版本数据库可通过自定义拆分字符串的存储过程/函数，生成带序号的结果集后按同样逻辑计算。

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