如何生成一百万个12位的随机数字

[CIOSOFT]

我修改了以下示例，但得不到。

===================================================================

http://topic.csdn.net/t/20050906/11/4252265.html
我搜索了一下，看了相关的帖子，可是看不懂。所以还是发帖问下，需要一个存储过程，主要功能生成2000个6位的随机数字编码存放在数据库中，具体如下：

id code status
1 123456 0
2 457890 0
3 566543 0
......

Table名称是TimeSquare_Code，id是自增量，code类型varchar(10),status类型bit(1)，默认值为0。
存储过程名字为SP_TimeSquare_randomcode
我是SQL初学者，很多东西都不懂，请各位写的尽量详细些，谢谢！

CREATE PROC SP_TimeSquare_randomcode
AS
INSERT TimeSquare_Code(code,status)
SELECT TOP 2000
Code=RIGHT(1000000+CAST(RAND(CHECKSUM(NEWID()))*1000000 as int),6),
status=0
FROM syscolumns a,syscolumns b

[wzy_love_sly]

偶想帮忙，不知道他要干什么内

[wzy_love_sly]

if object_id('TimeSquare_Code') is not null
drop table TimeSquare_Code
go
create table TimeSquare_Code(id int identity(1,1),code varchar(10),status bit)

insert into TimeSquare_Code(code,status)
select top 100 abs(checksum(newid()))%900000+100000,0
from sysobjects,syscolumns

select * from TimeSquare_Code

id code status
1 160229 0
2 867665 0
3 962538 0
4 624239 0
5 916242 0
6 233951 0
7 242639 0
8 402209 0
9 744471 0
10 935596 0
11 370928 0
12 578603 0
13 269882 0
14 213637 0
15 799781 0
16 855992 0
17 503376 0
18 450842 0
19 213853 0
20 226166 0
21 114206 0
22 852219 0
23 313280 0
24 589063 0
25 542104 0
26 961158 0
27 475382 0
28 661970 0
29 297468 0
30 490456 0
........

[-晴天]

create table TimeSquare_Code(id int identity(1,1),code int,status int default 0)
go
create procedure sp_TimeSquare_randomcode
as
insert into timesquare_Code(code)
select top 2000 code=right(1000000+CAST(RAND(CHECKSUM(NEWID()))*1000000  as  int),6)from syscolumns a,syscolumns b
go
exec sp_timesquare_randomcode
go
select * from timesquare_code
go
drop table timesquare_code
drop procedure sp_TimeSquare_randomcode

[wzy_love_sly]

--12位这样算
if object_id('TimeSquare_Code') is not null
drop table TimeSquare_Code
go
create table TimeSquare_Code(id int identity(1,1),code varchar(12),status bit)

insert into TimeSquare_Code(code,status)
select top 1000 (abs(checksum(newid()))%9+1)*100000000000+abs(checksum(newid()))%10000000000,0
from sysobjects,syscolumns

select * from TimeSquare_Code

id code status
1 601001088540 0
2 301010750578 0
3 600047385251 0
4 800985565511 0
5 701279177469 0
6 500966444797 0
7 701041263698 0
8 600799931258 0
9 101647238931 0
10 502114653943 0
11 200749206742 0
12 200724579533 0
13 600012001819 0
14 100214723996 0
15 700967497229 0
16 800517071021 0
17 101154710420 0
18 101741876240 0
19 901820690817 0
20 601690596926 0
21 601650801894 0
22 702012702077 0
23 700102815361 0
24 700899506280 0
25 401599988975 0
26 900110692081 0
27 500856152668 0
28 300578806638 0
29 900522655750 0
30 200833722309 0

[-晴天]

create table TimeSquare_Code(id int identity(1,1),code bigint,status int default 0)
go
create procedure sp_TimeSquare_randomcode
as
insert into timesquare_Code(code)
select top 1000000 code=900000000000*RAND(CHECKSUM(NEWID()))[phone] from syscolumns a,syscolumns b,syscolumns c
go
exec sp_timesquare_randomcode
go
select * from timesquare_code
go
drop table timesquare_code
drop procedure sp_TimeSquare_randomcode

[wzy_love_sly]

想随机的很平均得一点一点的加起来才行

[xiaoku]

学习的顶一下...

[ld2099]

学了

[CIOSOFT]

1、产生1000000个数会有重复吗？2、您在问题中提到“(1000000 行受影响)”这个是什么意思呢？
非常感谢

[rails2007]

mark

[hery2002]

[Quote=引用 12 楼 CIOSOFT 的回复:]
1、产生1000000个数会有重复吗？
-- NewID()不会有重复的,
-- 所以你产生的这1000000个是没有重复的.
2、您在问题中提到“(1000000 行受影响)”这个是什么意思呢？
-- 这个是一个返回信息,
-- 如果你执行了上面的语句,也会返回类似的信息,
-- 也就是产生了1000000个随机的数据而已
非常感谢
[/Quote]

[hyde100]

多少行受影响是数据库执行后的返回结果啊

[-晴天]

[Quote=引用 15 楼 hyde100 的回复:]
引用 9 楼 qianjin036a 的回复:
随机数基本算法:
(大数+小数-1)*伪随机函数+小数


这算法用在了上面了吗？
[/Quote]
12位的最大数是999999999999,最小数是100000000000
对比看一下:
900000000000*RAND(CHECKSUM(NEWID()))[phone]

[hyde100]

[Quote=引用 9 楼 qianjin036a 的回复:]
随机数基本算法:
(大数+小数-1)*伪随机函数+小数
[/Quote]

这算法用在了上面了吗？

[Herb2]

create table TimeSquare_Code(id bigint identity(1,1) PRIMARY KEY CLUSTERED,code bigint,status int default 0)
go
create procedure sp_TimeSquare_randomcode
as
insert into timesquare_Code(code)
select top 1000000 code =  cast(left(abs(checksum(newid())),6) as bigint)*1000000+left(abs(checksum(newid())),6)
from syscolumns a,syscolumns b,syscolumns c
go
exec sp_timesquare_randomcode
go
select * from timesquare_code
go
drop table timesquare_code
drop procedure sp_TimeSquare_randomcode

[-晴天]

create table TimeSquare_Code(id bigint identity(1,1) PRIMARY KEY CLUSTERED,code bigint,status int default 0)
go
create procedure sp_TimeSquare_randomcode
as
insert into timesquare_Code(code)
select top 1000000 code=900000000000*RAND(CHECKSUM(NEWID()))[phone] from syscolumns a,syscolumns b,syscolumns c
go
exec sp_timesquare_randomcode
go
select * from timesquare_code
go
drop table timesquare_code
drop procedure sp_TimeSquare_randomcode
/*
(1000000 行受影响)
id                   code                 status
-------------------- -------------------- -----------
1                    179955464359         0
2                    946746709785         0
3                    842256174490         0
4                    406017229245         0
5                    909918612757         0
6                    853429739130         0
7                    418210712692         0
8                    276307333223         0
9                    916767338201         0
10                   379883097336         0
11                   670570745597         0
12                   910665877885         0
13                   775075604307         0
14                   376190829494         0
15                   765257896361         0
16                   193355555489         0
17                   271115565074         0
18                   870975784131         0
19                   624525856041         0
20                   441185349817         0
21                   929168221789         0
22                   606124832166         0
23                   636647050600         0
24                   206686120407         0
25                   408298985774         0
26                   513568664671         0
27                   144864558300         0
28                   518691451850         0
29                   318376229182         0
30                   754150583543         0
31                   296943155269         0
32                   669389195194         0
33                   588398267788         0
34                   315379461509         0
35                   482198205995         0
36                   971027702597         0
37                   659188979505         0
38                   563952234879         0
39                   112929498384         0
40                   743843849369         0
41                   671790529633         0
42                   663920384602         0
43                   676032719489         0
44                   405609981896         0
45                   418409134139         0
46                   362045460165         0
47                   225667368972         0
48                   172056184251         0
49                   795155655448         0
50                   612998815219         0
51                   823785507895         0
52                   854492250579         0
53                   642888800928         0
54                   986114233949         0
55                   199681911913         0
56                   946281598804         0
57                   397405691448         0
58                   448505023400         0
59                   435153669266         0
60                   679456729123         0
61                   997191721021         0
62                   483205186077         0
63                   387823377944         0
64                   518340820067         0
65                   492813978433         0
66                   133394068924         0
67                   356748411119         0
68                   442159311905         0
69                   466630298998         0
70                   333333035492         0
71                   528858367075         0
72                   976792733008         0
73                   899532367428         0
74                   417027949847         0
75                   721189152011         0
76                   789074362410         0
77                   131771537782         0
78                   301510246980         0
79                   104034712601         0
80                   877014300544         0
81                   133126568020         0
82                   522319711707         0
83                   402659575368         0
84                   920977560735         0
85                   959992148511         0
86                   844814389662         0
87                   853378679508         0
88                   880764202773         0
89                   637382291468         0
90                   914445453310         0
91                   738653072380         0
92                   932699248632         0
93                   355403862450         0
94                   453675354412         0
95                   273520313043         0
96                   128227683274         0
97                   222714582403         0
98                   201425630290         0
99                   937310583801         0
100                  318355214912         0
101                  953635695188         0
102                  394284116395         0
103                  992466275877         0
104                  969750277888         0
105                  234067890675         0
106                  795499396468         0
107                  467809646636         0
108                  509419648053         0
109                  874616686413         0
110                  273268918386         0
111                  417161965586         0
112                  672340557258         0
113                  743577612876         0
114                  534287686748         0
115                  528715725519         0
116                  533519517572         0
117                  473582846469         0
118                  118396911712         0
119                  531405633385         0
120                  991028208636         0
121                  717305934770         0
122                  530048036189         0
123                  314779391825         0
124                  434030485829         0
125                  422617214258         0
......
*/

[-晴天]

随机数基本算法:
(大数+小数-1)*伪随机函数+小数

[wzy_love_sly]

哦 不错，我还非拿整数乘呢，直接利用小数了 学习了

[lao_bulls]

学习。

[CIOSOFT]

只产生了这麽多，是什么原因呢？sql 2005
(811801 行受影响)