笔记165 生成随机数的5种方法Rand() random
1 --生成随机数的5种方法Rand() random
2
3 Method 1 : Generate Random Numbers (Int) between Rang
4 -- Create the variables for the random number generation
5 DECLARE @Random int;
6 DECLARE @Upper int;
7 DECLARE @Lower int
8
9 -- This will create a random number between 1 and 999
10 SET @Lower = 1 -- The lowest random number
11 SET @Upper = 999 -- The highest random number
12 SELECT @Random = Round(((@Upper - @Lower -1) * Rand() + @Lower), 0)
13 SELECT @Random
14
15
16 Method 2 : Generate Random Float Numbers
17 SELECT RAND( (DATEPART(mm, GETDATE()) * 100000 )
18 + (DATEPART(ss, GETDATE()) * 1000 )
19 + DATEPART(ms, GETDATE()) )
20
21
22 Method 3 : Random Numbers Quick Scripts
23 -- random float from 0 up to 20 - [0, 20)
24 SELECT 20*RAND()
25
26
27 -- random float from 10 up to 30 - [10, 30)
28 SELECT 10 + (30-10)*RAND()
29
30
31 -- random integer between 0 and 20 - [0, 20]
32 SELECT CONVERT(int, (20+1)*RAND())
33
34
35 -- random integer between 10 and 30 - [10, 30]
36 SELECT 10 + CONVERT(int, (30-10+1)*RAND())
37
38
39 Method 4 : Random Numbers (Float, Int) Tables Based with Time
40 DECLARE @t table( randnum float )
41 DECLARE @cnt int;
42 set @cnt = 0
43 WHILE @cnt <=10000
44 BEGIN
45 set @cnt = @cnt + 1
46 INSERT INTO @t
47 SELECT RAND( (DATEPART(mm, GETDATE()) * 100000 )
48 + (DATEPART(ss, GETDATE()) * 1000 )
49 + DATEPART(ms, GETDATE()) )
50 END
51
52 SELECT randnum, count(*)
53 FROM @t
54 GROUP BY randnum
55
56
57 Method 5 : Random number on a per row basis
58 -- The distribution is pretty good however there are the occasional peaks.
59 -- If you want to change the range of values just change the 1000 to the maximum value you want.
60 -- Use this as the source of a report server report and chart the results to see the distribution
61
62
63 SELECT randomNumber, Count(1) countOfRandomNumber
64 FROM (SELECT abs(cast(newid() as binary(6)) %1000) + 1 randomNumber
65 FROM sysobjects) sample
66 GROUP BY randomNumber
67 ORDER BY randomNumber
