SQL Query to fetch numbers in given steps between

问题:

I have a set of data like this:

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

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.

回答1:

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

Or a recursive CTE

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

回答2:

See this useful function

CREATE FUNCTION [dbo].[Sequence](@min INT, @max INT, @step INT)
RETURNS @ret TABLE (id INT PRIMARY KEY)
AS
BEGIN
    WITH numbers(id) as
    (
        SELECT @min id
        UNION ALL 
        SELECT id+@step
        FROM numbers
        WHERE id < @max
    )
    INSERT @ret 
    SELECT id FROM Numbers
    OPTION(MAXRECURSION 0)
    RETURN
END

回答3:

@Mikael Eriksson already mentioned a numbers table / tally table (search for it online, there are LOTS of possible uses, many DBAs always want a tally table to be present in any system they manage)

I just wanted to share one non-recursive CTE-based "tally table" solution that I saw online the other day, that I think it amazingly elegant for its huge range (4 thousand million logical rows) and general applicability without any database dependencies:

WITH 
        E00(N) AS (SELECT 1 UNION ALL SELECT 1),
        E02(N) AS (SELECT 1 FROM E00 a, E00 b),
        E04(N) AS (SELECT 1 FROM E02 a, E02 b),
        E08(N) AS (SELECT 1 FROM E04 a, E04 b),
        E16(N) AS (SELECT 1 FROM E08 a, E08 b),
        E32(N) AS (SELECT 1 FROM E16 a, E16 b),
   cteTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY N) FROM E32)
SELECT *
FROM cteTally
WHERE N >= 2500 
    AND N <= 2700
    AND N % 10 = 0

I found it here, but I don't know whether that's the original source of this CTE.

The nice thing about it is that you don't need to worry about min, max, or step size, and yet it performs very well in most (one-off) situations. That said, it should NOT be used in any frequently-called business process; Any physical indexed numbers table will always perform better!

EDIT: I just searched a little more for the source of this method (I had missed the stackoverflow link in the article I referenced), and apparently it's originally attributed to Itzik Ben-Gan, from the bottom of page 255 in a book titled "Inside Microsoft SQL Server 2005 - T-SQL Querying" (says Jeff Moden, who I implicitly trust).