SQL查询以在范围之间的给定步骤中获取数字 [英] SQL Query to fetch numbers in given steps between a range

问题描述

我有一组这样的数据:

MinNo: 2500
MaxNo: 2700
IncrementStep: 10

在最小数字和最大数字之间，将列出具有给定步骤的所有可能数字的列表，如下所示:

Between the minimum number and the maximum number a list of all possible numbers with the given step are to be listed as shown below:

2500
2510
2520
2530
2540
2550
2560
2570
2580
2590
2600
2610
2620
2630
2640
2650
2660
2670
2680
2690
2700

我知道这可以使用 while 循环来实现.请让我知道这是否可以通过使用公用表表达式(如果需要)的选择查询来完成.提前致谢.

I'm aware that this can be achieved using a while loop. Kindly let me know if this can be done using a select query using Common Table Expressions (if needed). Thanks in advance.

推荐答案

您可以使用数字表(或 master..spt_values).

You can use a numbers table (or master..spt_values).

declare @MinNo int
declare @MaxNo int
declare @IncrementStep int

set @MinNo = 2500
set @MaxNo = 2700
set @IncrementStep = 10

select @MinNo + Number * @IncrementStep
from master..spt_values
where type = 'P' and
      number between 0 and (@MaxNo - @MinNo) / @IncrementStep

或递归 CTE

;with C as
(
  select @MinNo as Num
  union all 
  select Num + @IncrementStep
  from C
  where Num < @MaxNo
)      
select Num
from C

这篇关于SQL查询以在范围之间的给定步骤中获取数字的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！