Sql 随机数 且不在记录中(抽奖)

表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()

　　

　　

 

转载于:https://www.cnblogs.com/Lance-Lan/p/3360262.html