不用这么复杂 你只需要做个数据类型转换就可以了: select sum(cast(a as money)) as total from table1a 是table1的字段 a的值可以为: 1 2 333 0 11,256
case(字段名 as numeric(10,4))
我终于想出来了,用函数来解决,先创建函数: CREATE FUNCTION DBO.ChangSum(@str VarChar(32)) RETURNS int AS BEGIN Declare @Result int Set @Result=0 while(CharIndex(',',@str)>0) begin set @Result=@Result+cast(substring(@str,1,CharIndex(',',@str)-1) as int) set @str=substring(@str,CharIndex(',',@str)+1,len(@str)) end set @Result=@Result+cast(@str as int) Return(@Result) END GO单个调用: select DBO.ChangSum('100,804,1') select Sum(DBO.ChangSum(字段名)) from TableName
你只需要做个数据类型转换就可以了:
select sum(cast(a as money)) as total from table1a 是table1的字段
a的值可以为:
1
2
333
0
11,256
CREATE FUNCTION DBO.ChangSum(@str VarChar(32))
RETURNS int
AS
BEGIN
Declare @Result int
Set @Result=0
while(CharIndex(',',@str)>0)
begin
set @Result=@Result+cast(substring(@str,1,CharIndex(',',@str)-1) as int)
set @str=substring(@str,CharIndex(',',@str)+1,len(@str))
end
set @Result=@Result+cast(@str as int)
Return(@Result)
END
GO单个调用:
select DBO.ChangSum('100,804,1')
select Sum(DBO.ChangSum(字段名)) from TableName
45,12 转换成了57,
14,22 转换成了56
我要效果是分别为: 4512,1422