允许重复参考:declare @t table(i int) insert @t select abs(checksum(newid()))%9000+1000select * from @t /* i ----------- 4808(1 行受影响) */
declare @t table(i int) insert @t select abs(checksum(newid()))%90000+10000select * from @t /* i ----------- 17089(1 行受影响) */不能确保百分百不重复,如果要求不严格的话可以使用。
declare @n char(10) declare @f bit set @f=0 while @f=0 begin select @n=cast(rand() as char(10)) if (substring(@n,3,1)<>'0') set @f=1 end print substring(@n,3,4)
--四位随机整数的生成 select id = cast(RAND() * 10000 as int)--五位随机整数的生成 select id = cast(RAND() * 100000 as int)
select id = cast(RAND() * 1000 as int) select id = cast(RAND() * 10000 as int)
借用5楼的方法,生成4位或5位随机数的代码declare @t table(i int) insert @t select abs(checksum(newid()))%99000+1000select * from @t /* i ----------- 5911(1 行受影响) */
用rand()函数,他的取值范围为0到1
SELECT n, --1到10的10行记录 rand(), --返回同一个随机数 ABS(CHECKSUM(NEWID())) --返回10个不同的随机数 FROM Nums WHERE n BETWEEN 1 AND 10rand()在每一个语句中只能生成一个随机数。ABS(CHECKSUM(NEWID()))更灵活一点,而且随机分布性也很好。字段默认值用rand(),结果也一样:CREATE TABLE TestRand( ID int NOT NULL PRIMARY KEY CLUSTERED, RandNum int NOT NULL DEFAULT(CAST(RAND() * 1000 AS int)), NewIDNum int NOT NULL DEFAULT(ABS(CHECKSUM(NEWID())) % 9000 + 1000) ) GO INSERT INTO TestRand(ID) SELECT n FROM Nums WHERE n BETWEEN 1 AND 10 GO
insert @t select abs(checksum(newid()))%9000+1000select * from @t
/*
i
-----------
4808(1 行受影响)
*/
declare @t table(i int)
insert @t select abs(checksum(newid()))%90000+10000select * from @t
/*
i
-----------
17089(1 行受影响)
*/不能确保百分百不重复,如果要求不严格的话可以使用。
declare @f bit
set @f=0
while @f=0
begin
select @n=cast(rand() as char(10))
if (substring(@n,3,1)<>'0')
set @f=1
end
print substring(@n,3,4)
select id = cast(RAND() * 10000 as int)--五位随机整数的生成
select id = cast(RAND() * 100000 as int)
select id = cast(RAND() * 1000 as int)
select id = cast(RAND() * 10000 as int)
insert @t select abs(checksum(newid()))%99000+1000select * from @t
/*
i
-----------
5911(1 行受影响)
*/
SELECT
n, --1到10的10行记录
rand(), --返回同一个随机数
ABS(CHECKSUM(NEWID())) --返回10个不同的随机数
FROM Nums
WHERE n BETWEEN 1 AND 10rand()在每一个语句中只能生成一个随机数。ABS(CHECKSUM(NEWID()))更灵活一点,而且随机分布性也很好。字段默认值用rand(),结果也一样:CREATE TABLE TestRand(
ID int NOT NULL PRIMARY KEY CLUSTERED,
RandNum int NOT NULL DEFAULT(CAST(RAND() * 1000 AS int)),
NewIDNum int NOT NULL DEFAULT(ABS(CHECKSUM(NEWID())) % 9000 + 1000)
)
GO
INSERT INTO TestRand(ID)
SELECT n
FROM Nums
WHERE n BETWEEN 1 AND 10
GO