我有一张和下面一样的桌子
numbers
794
709090
我需要的是所有数字的总和,例如
7+9+4=20;2+0=2
或
7+0+9+0+9+0=25;2+5=7
.
我尝试了下面的脚本,但不知何故不起作用:
declare @t table(numbers int)
insert into @t select 794 union all select 709090
declare @maxValue int
select @maxValue = max(numbers) from @t
;with cte as(
SELECT SUM(CAST(SUBSTRING(cast(numbers as varchar(1000)),number,1) AS INT)) SUMOFDIGITS FROM @t
cross apply (
SELECT DISTINCT number FROM
MASTER..SPT_VALUES WHERE number > 0 AND number <= DATALENGTH(@maxValue) ) x)
select SUMOFDIGITS, finalsum = cast(left(SUMOFDIGITS,1) as int)+cast(right(SUMOFDIGITS,1) as int)
from cte
