在 Sql Server 中创建序列 [英] Creating Sequence in Sql Server

问题描述

我想在 SQL Server 中创建一个具有最小值和最大值的数字序列.如果数量达到最大限制，我想循环.任何机构可以帮助我吗??

I want to create a sequence of numbers in SQL Server that will have a minimum value and maximum value. I want to cycle if the number reaches the maximum limit. Can any body help me??

推荐答案

不需要 while 循环.首先，您需要一个 Tally 或 Numbers 表:

There is no need for a while loop. First, you need a Tally or Numbers table:

Create Table dbo.Numbers ( Value int not null Primary Key Clustered )
GO
With Nums As
    (
    Select Row_Number() Over( Order By S1.object_id ) As Num
    From sys.columns as s1
        cross join sys.columns as s2
    )
Insert dbo.Numbers( Value )
Select Num
From Nums
Where Num <= 100000

我的表中只放了 10 万个数字，但您可能需要更多.您只需填充此表一次.现在您可以创建您想要的任何序列.像这样:

I only put a 100K of numbers in my table but you might need more. You need only populate this table once.Now you can create any sequence you desire. Like so:

Select Value
From dbo.Numbers
Where Value Between @Start And @End

想要一个增量值?:

Select Value
From dbo.Numbers
Where Value % @Increment = 0

这篇关于在 Sql Server 中创建序列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！