我有两个值,数据类型为小数(15,2)
例如:
@from='19.99'
和
@to='20.02'
我需要返回这两个值之间的范围。
期望输出-
19.99,20.00,20.01,20.02
我有两个值,数据类型为小数(15,2)
例如:
@from='19.99'
和
@to='20.02'
19.99,20.00,20.01,20.02
DECLARE @from DECIMAL(10,2) , @to DECIMAL(10,2), @interval DECIMAL(10,2)
SET @from=19.99
SET @to= 20.02
SET @interval=0.01
SELECT @from +r*@interval
FROM
(
SELECT TOP (SELECT CAST((@to-@from)/@interval AS INT)+1)
ROW_NUMBER() OVER( ORDER BY (SELECT 1)) -1 r
FROM
sys.objects s1 CROSS JOIN sys.objects s2
)T
尝试:
DECLARE @From DECIMAL (15,2) = 19.99;
DECLARE @To DECIMAL (15,2) = 20.02;
DECLARE @Step DECIMAL (15,2) = 00.01;
DECLARE @R TABLE (Value DECIMAL(15,2));
WHILE @From <= @To
begin
INSERT INTO @R VALUES (@From);
SET @From = @From + @Step;
End
SELECT *
FROM @R;
结果:
+=======+
| Value |
+=======+
| 19,99 |
+-------+
| 20,00 |
+-------+
| 20,01 |
+-------+
| 20,02 |
+-------+
使用 CTE
:
DECLARE @From DECIMAL (15,2) = 19.99;
DECLARE @To DECIMAL (15,2) = 20.02;
DECLARE @Step DECIMAL (15,2) = 00.01;
WITH CTE AS(
SELECT @Step * 1 Val
UNION ALL
SELECT @Step * 2
UNION ALL
SELECT @Step * 3
UNION ALL
SELECT @Step * 4
)
SELECT @From - @Step + CTE.Val Value FROM CTE;
结果:
+=======+
| Value |
+=======+
| 19,99 |
+-------+
| 20,00 |
+-------+
| 20,01 |
+-------+
| 20,02 |
+-------+
with cte as (
select @from as val
union all
select cast(val + 0.01 as decimal(15,2))
from cte
where density < @to
)
select val
from cte
option (MAXRECURSION 0);