如何在SQL中对逗号分隔的字符串求和？ [重复]

This question already has an answer here:

这个问题在这里已有答案:

Summing a comma separated column in MySQL 4 (not 5) 4 answers

总结MySQL 4中的逗号分隔列(不是5)4个答案

id  value
1   1,2,3,4
2   2,3,4

So I want to get this result:

所以我想得到这个结果:

id   sum
1     10
2     9

Can I do it in SQL(MySQL)?

我可以在SQL(MySQL)中做到吗?

2 个解决方案

#1

you can do it more dynamically Creating a function. Please follow the following steps

你可以更动态地创建一个函数。请按照以下步骤操作

create a function that give the sum of a comma separated value

创建一个函数,给出逗号分隔值的总和

CREATE FUNCTION GetToalOfCommaSeperatedVal 
(
  @commaSeperatedVal varchar(100)
)
RETURNS int
AS
BEGIN

    declare @sum int
    DECLARE @x XML 
    SELECT @x = CAST('<A>'+ REPLACE(@commaSeperatedVal,',','</A><A>')+ '</A>' AS XML)

    SELECT @sum=sum(t.value('.', 'int')) 
      FROM @x.nodes('/A') AS x(t)

   return @sum
END
GO

the do a just select command in the following way

以下列方式执行just select命令
```
select id,dbo.GetToalOfCommaSeperatedVal(value) from YOUR_TABLE
```

#2

With great effort, you can do this. Really, though, this is a very, very bad way to store data.

经过努力,你可以做到这一点。但实际上,这是一种非常非常糟糕的数据存储方式。

In the spirit that sometimes we have to use data whose format is not under our control:

本着这种精神,有时我们必须使用格式不受我们控制的数据:

select id,
       (substring_index(value, ',', 1) +
        substring_index(substring_index(concat(value, ',0'), ',', 2), ',', -1) +
        substring_index(substring_index(concat(value, ',0'), ',', 3), ',', -1) +
        substring_index(substring_index(concat(value, ',0'), ',', 4), ',', -1) +
        substring_index(substring_index(concat(value, ',0'), ',', 5), ',', -1)
       ) as thesum
from t;

The nested called to substring_index() fetch the nth value in the string. The concat(value, ',0') is to handle the case where there are fewer values than expressions. In this case, the nested substring_index() will return the last value for any value of n greater than the number of items in the list. Concatenating 0 to the list ensures that this doesn't affect the sum.

嵌套的调用substring_index()获取字符串中的第n个值。 concat(value,',0')用于处理值比表达式少的情况。在这种情况下,嵌套的substring_index()将返回任何n值大于列表中项目数的最后一个值。将0连接到列表可确保这不会影响总和。

The SQL Fiddle is here.

SQL Fiddle就在这里。

#1