亲爱的所有 在我的表MyTable中,我有一个名为MyColumn的列,其中包含8位数字值。 (即) MyColumn 20151201 20151204 20181208 20181219 我的要求是我需要一个名为SumColumn的新列,数据应该是使用此公式的MyColumn值的总和。 br $> (即) MyColumn SumColumn 20151201 3 20151204 6 20181208 4 20181219 6 公式为 例如 20181219 (2 + 0 + 1 + 8)+(1+ 2)+(1 + 9) (11)+(3)+(10) (1 + 1)+(3)+(1 + 0) (2)+(3)+(1) (6) 帮助到达这个。
解决方案您可以创建一个将返回的函数添加数字。这是我创建的函数: 创建 FUNCTION fn_AddNumbers( @ inputStr VARCHAR ( 50 )) RETURNS INT AS BEGIN DECLARE @ finalVal INT SET @ finalVal = 0 DECLARE @ j TINYINT DECLARE @ i TINYINT SET @ j = 1 SET @ i = 1 WHILE ( @ i < = LEN( @ finalVal )) BEGIN SET @ j = 1 SET @ finalVal = 0 WHILE ( @ j < = LEN( @ inputStr )) BEGIN SET @ finalVal = @ finalVal + CAST(SUBSTRING( @ inputStr , @ j , 1 ) AS TINYINT ) SET @ j = @ j + 1 END SET @ inputStr = @ finalVal SET @ i = @ i + 1 END RETURN @ finalVal END
然后调用它select语句中的函数。
SELECT dbo.fn_AddNumbers(' 12345') AS ' 数字总和'祝你好运。
Dear all In my table MyTable I have a column named MyColumn which contains 8 digit numeric value. (ie) MyColumn 20151201 20151204 20181208 20181219 My requirement is I need a new column named SumColumn and the data should be sum of MyColumn values with this formula. (ie) MyColumn SumColumn 20151201 3 20151204 6 20181208 4 20181219 6 Formula is For Example 20181219 (2+0+1+8) + (1+2) + (1+9) (11) + (3) + (10) (1+1) + (3) + (1+0) (2) + (3) +(1) (6) Help to arrive this one.
解决方案 You can create a function which will return the addition of numbers. Here is the function i have created : CREATE FUNCTION fn_AddNumbers (@inputStr VARCHAR(50)) RETURNS INT AS BEGIN DECLARE @finalVal INT SET @finalVal = 0 DECLARE @j TINYINT DECLARE @i TINYINT SET @j = 1 SET @i = 1 WHILE (@i <= LEN(@finalVal)) BEGIN SET @j = 1 SET @finalVal = 0 WHILE (@j <= LEN(@inputStr)) BEGIN SET @finalVal = @finalVal + CAST(SUBSTRING(@inputStr, @j, 1) AS TINYINT) SET @j = @j + 1 END SET @inputStr = @finalVal SET @i = @i + 1 END RETURN @finalVal ENDThen call this function in your select statement.
SELECT dbo.fn_AddNumbers('12345') AS 'Sum Of Numbers'Good luck.
更多推荐
如何在sql中的数字字符串中添加所有数字
发布评论