不使用NewID()的SQL上的随机数 [英] Random Number on SQL without using NewID()

问题描述

您好，我想使用以下语句生成唯一的随机数:

Hello I want to generate a Unique Random number with out using the follow statement :

Convert(int, (CHECKSUM(NEWID()))*100000) AS [ITEM] 

当我在"from"上使用joins子句时，它会通过使用NEWID()来生成双重寄存器

Cause when I use joins clauses on "from" it generates double registers by using NEWID()

我正在使用SQL Server 2000

Im using SQL Server 2000

* PD:当我使用Rand()时，它可能会以1的100000000概率重复出现，但这非常关键，因此重复生成的随机值必须是0％的概率

*PD : When I use Rand() it probably repeat on probability 1 of 100000000 but this is so criticall so it have to be 0% of probability to repeat a random value generated

我的带有NewID()的查询和SELECT语句上的结果重复了(x2) 我没有NewID()并在SELECT语句上使用Rand()的查询是单(x1)，但是重复生成随机值的可能性不确定，但是存在！

My Query with NewID() and result on SELECT statement is duplicated (x2) My QUery without NewID() and using Rand() on SELECT statement is single (x1) but the probability of repeat the random value generated is uncertainly but exists!

谢谢！

推荐答案

是否溢出?

CAST(CHECKSUM(NEWID()) AS bigint) * CAST(100000 AS bigint) AS [ITEM]

CAST(CAST(CHECKSUM(NEWID()) AS bigint) * CAST(100000 AS bigint) % 2100000000 AS int) AS [ITEM]

不存在号码重复概率为0％的情况

There is no such thing as 0% chance of duplicated number

CHECKSUM(NEWID()))返回一个整数，该整数具有40亿行. 生日悖论意味着发生碰撞的可能性当然要高得多.

CHECKSUM(NEWID())) returns an integer, which has 4 billion rows. The birthday paradox means the chance of collision is much higher of course.

Bigint(上方)或十进制(38,0)为您提供了更多的游戏空间，但只会减少发生碰撞的机会，但永远不会消除.

Bigint (above) or decimal(38,0) give you a lot more room to play with but only reduce the chance of collision but never eliminate.

但是仍然不知道为什么要尝试加入唯一的随机数...

But still don't get why you're trying to JOIN in a unique randomnumber...

这篇关于不使用NewID()的SQL上的随机数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！