SQL Server generates random numbers in the specified range

Many times in development, we encounter this need to generate a specified range of random number cases. And in many languages, such as Java, C #, SQL, and so on, there will be a function to generate a random decimal like 0.234273983423789, and all the random number is through the most basic random number (0.234273983423789) change over.

Let me say the idea of generating a specified range of random numbers, for example, I'm going to generate a random number in a 100-999 range, I'm going to make sure I write the value generated by the expression that generates the random number, the maximum is 999, and the minimum is 100. And to understand a little truth in mathematics, 0.99 to multiply a number results in about equal to this number but not to the number. What do you mean? Is the result of 0.999*9000 is certainly equal to 8999.9999 ... , use this idea for a while.

Here I'll introduce two functions, which will be used in a moment.
Floor (): Returns a minimum integer less than or equal to the current number floor (2.5) =2
Ceiling (): In contrast to the previous function, returns a maximum integer greater than or equal to the current number ceiling (2.5) =3

Last look at the code (randomly generate a random number of 100-999)

Generate a random number of 100-999, including 100 and 999

SELECT  Floor (RAND()*) +  -

Now let's analyze this code, first from the Inside, rand () will generate a 0.000000. To 0.999999.. Random number, then this random number multiplied by 900 will generate a 0 to 899 ... Random number, and at the end of adding 100 to it, generates a random number of 100 to (899+100) that is 100 to 999.

The following code can output all random numbers until a 100 or 999 stop cycle occurs.

1 Declare @rand int2  while 1=13     BEGIN4     SET @rand= Floor(RAND()* the)+ -5     PRINT @rand6     IF @rand= - or @rand=9997         begin8          Break9         EndTen     End

If there is a better way, you can comment on the private messages! Communicate with each other!

There is a better way, but not very well understood, you can write down.

1 DECLARE @Result INT2 DECLARE @Upper INT3 DECLARE @Lower INT4  5 SET @Lower = 1      --including the number on the left6  7 SET @Upper =  One     --does not include the number on the right8  9                     --[Left,right]Ten   One SELECT @Result = ROUND(((@Upper - @Lower -1)* RAND()+ @Lower),0) A SELECT @Result

Author contact information: [Email protected]

If forwarding please be sure to indicate the original address thank you!

SQL Server generates random numbers in the specified range