php - mysql select查询中逗号分隔值的总和

Question

我有一张如下查询的表

mysql> select *from DemoTable;

这将产生以下输出 -

+--------------------+
| ListOfValues       |
+--------------------+
| 20, 10, 40, 50, 60 |
+--------------------+
1 row in set (0.00 sec)

预期输出如下。

+----------+
| TotalSum |
+----------+
|      180 |
+----------+
1 row in set (0.00 sec)

我尝试了下面的示例，但没有一个起作用。

SELECT SUM(replace(ListOfValues, ',', '')) as TotalSum FROM DemoTable;

这里只有第一个值出现在输出中，总和不起作用。

+----------+
| TotalSum |
+----------+
|      20 |
+----------+
1 row in set (0.00 sec)

谁能帮忙。我尝试了很多来自 stackoverflow 的示例，但没有用。

Answer 1

正如评论中提到的，您应该重新设计数据库，不要在列中使用逗号分隔的列表。

如果这是不可能的，这里有一个使用 json_table 的解决方法:

select t.ListOfValues, sum(j.val)
from mytable t
join json_table(
  CONCAT('[', t.ListOfValues, ']'),
  '$[*]' columns (val int path '$')
) j
group by t.ListOfValues

Demo here

如果存在重复的ListOfValues，则可以使用辅助列为每行提供唯一的序列号:

with cte as (
  select *, row_number() over( order by ListOfValues) as rn
  from mytable
)
select t.ListOfValues, sum(j.val)
from cte t
join json_table(
  CONCAT('[', t.ListOfValues, ']'),
  '$[*]' columns (val int path '$')
) j
group by t.rn, t.ListOfValues

查看演示:https://dbfiddle.uk/gcUbdS0U