表Luck 是存放已被使用的数字
CREATE TABLE [Luck](
[Id] [bigint] IDENTITY(1,1) NOT NULL,
[Key] [bigint] NULL,
)
插入几条数据
Insert Into Luck(Key) Values(1)
Insert Into Luck(Key) Values(2)
Insert Into Luck(Key) Values(3)
Insert Into Luck(Key) Values(5)
查询 1-10且不存在 Luck中 借助 master.dbo.spt_values
select top 1 number from master.dbo.spt_values where type='P' and number between 1 and 10 and number not in (select [Key] from Luck) order by NEWID()
但是 master.dbo.spt_values 局限性 最大只能2047
当超过2047 可以自己创建一个表
CREATE TABLE [Number](
[Num] [bigint] NULL
)
然后在Number表中插入连续的1到你所需要的最大值
例如 1-10
Insert Into Number(Num) Values(1)
Insert Into Number(Num) Values(2)
Insert Into Number(Num) Values(3)
Insert Into Number(Num) Values(4)
Insert Into Number(Num) Values(5)
Insert Into Number(Num) Values(6)
Insert Into Number(Num) Values(7)
Insert Into Number(Num) Values(8)
Insert Into Number(Num) Values(9)
Insert Into Number(Num) Values(10)
select top 1 Num from Number where Num not in (select [Key] from Luck) and num between 1 and 10 order by NEWID()