金额分解 - SQL

Question

我需要将一个数量(数字(分解成组件，例如，将 58963 的数量分解为 50000、8000、900、60 和 3，我如何使用函数在 sql 中实现这一点，有什么想法吗？

问候

伊尔凡

Answer 1

尝试

DECLARE @amount INT = 58963
DECLARE @temp INT
DECLARE @i INT = 0
DECLARE @tempStore TABLE(val int)
WHILE(@amount>0)
BEGIN
SET @temp = @amount % 10;
SET @amount = @amount /10;
INSERT INTO @tempStore VALUES(@temp * POWER(10, @i))
SET @i = @i+1;
END
SELECT * FROM @tempStore ORDER BY val DESC

如果值不适合整数范围，则可以使用bigint而不是int。

如果要获取逗号分隔的输出，请尝试

DECLARE @amount BIGINT = 589625
DECLARE @temp BIGINT
DECLARE @i INT = 0
DECLARE @tempStore TABLE(val BIGINT)
WHILE(@amount>0)
BEGIN
SET @temp = @amount % 10;
SET @amount = @amount /10;
INSERT INTO @tempStore VALUES(@temp * POWER(10, @i))
SET @i = @i+1;
END
DECLARE @tmp VARCHAR(MAX)= ''
select @tmp = @tmp + CONVERT(VARCHAR(100), val ) + ', ' from @tempStore ORDER BY val DESC
select SUBSTRING(@tmp, 0, LEN(@tmp))

如果你想用数据库的方式而不是一般的编程逻辑来实现这一点(不想要WHILE循环(，那么你可以尝试

DECLARE @amount AS INT = 58963;
;WITH cte
AS ( SELECT  1 AS n, @amount AS m, 1 as p, 1 as r
UNION ALL
SELECT  (cte.m%10), (cte.m / 10), (cte.p*10), ((cte.m%10) * (cte.p*10))/10
FROM cte
WHERE cte.m > 0
)
SELECT cte.r --,*
FROM    cte WHERE (cte.n*cte.p)/10 > 0
--ORDER BY cte.r DESC

Answer 2

对于 SQL Server

DECLARE @num INT = 58963;
with decList as
(
select 10 AS decNum
union all
select decNum  * 10 AS dt
from decList
where decNum  <= @num
)
select CASE WHEN decNum > 10 
THEN ((@num % decNum) / (decNum / 10)) * (decNum / 10)
ELSE @num % decNum
END
from decList

Answer 3

这应该适用于任何数字。

DECLARE @amount AS INT = 58963;
WITH    cte
AS ( SELECT   1 AS n
UNION ALL
SELECT   cte.n * 10
FROM     cte
WHERE    cte.n < @amount / 10
)
SELECT  ( ( @amount % ( cte.n * 10 ) ) / cte.n ) * cte.n
FROM    cte;