如何在SQL中加逗号分隔的字符串?

问题描述：

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

所以我想得到这个结果:

So I want to get this result:

id   sum
1     10
2     9

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

Can I do it in SQL(MySQL)?

答

您可以更加动态地创建函数.请按照以下步骤操作

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

select id,dbo.GetToalOfCommaSeperatedVal(value) from YOUR_TABLE

如何在SQL中加逗号分隔的字符串?

相关推荐