insert tb select top 1 right('00'+ltrim(number),2) from master..spt_values where type='P' and number between 1 and 31 order by newdid()
select right('00'+ltrim(cast(rand()*(31-1)+1 as decimal(38,0))),2) /* ---- 06*/
create table [tb]([col] varchar(2))insert tb select top 1 right('00'+ltrim(number),2) from master..spt_values where type='P' and number between 1 and 31 order by newid()
---查询--- select * from [tb] ---执行以上语句7次以后的结果--- col ---- 13 28 22 23 26 26 20(所影响的行数为 7 行)
CREATE TABLE SignUp(ID INT AUTO_INCREMENT NOT NULL PRIMARY KEY,Email VARCHAR(255),State VARCHAR(20) DEFAULT 'China',City INT(2) ZEROFILL);LOAD DATA INFILE 'G:\\data.txt' INTO TABLE SignUp(Email);UPDATE SignUp SET City=RAND()*31;SELECT * FROM SignUp;1,[email protected],China,24 2,[email protected],China,22 3,[email protected],China,06 4,[email protected] ,China,27 ---------------- 如果不行.City用INT类型如果还不行.加一个TRUNCATE函数. UPDATE SignUp SET City=TRUNCATE(RAND()*31);
select top 1 right('00'+ltrim(number),2)
from master..spt_values
where type='P' and number between 1 and 31
order by newdid()
/*
----
06*/
select top 1 right('00'+ltrim(number),2)
from master..spt_values
where type='P' and number between 1 and 31
order by newid()
---查询---
select * from [tb]
---执行以上语句7次以后的结果---
col
----
13
28
22
23
26
26
20(所影响的行数为 7 行)
CREATE TABLE SignUp(ID INT AUTO_INCREMENT NOT NULL PRIMARY KEY,Email VARCHAR(255),State VARCHAR(20) DEFAULT 'China',City INT(2) ZEROFILL);LOAD DATA INFILE 'G:\\data.txt'
INTO TABLE SignUp(Email);UPDATE SignUp SET
City=RAND()*31;SELECT * FROM SignUp;1,[email protected],China,24
2,[email protected],China,22
3,[email protected],China,06
4,[email protected] ,China,27
----------------
如果不行.City用INT类型如果还不行.加一个TRUNCATE函数.
UPDATE SignUp SET
City=TRUNCATE(RAND()*31);
郁闷,报错了:服务器: 消息 170,级别 15,状态 1,行 1
第 1 行: 'AUTO_INCREMENT' 附近有语法错误。
服务器: 消息 170,级别 15,状态 1,行 3
第 3 行: 'DATA' 附近有语法错误。
declare @t table (id int)
set @i=0
while @i<100
begin
insert @t select floor((rand()+0.1)*31)-2
set @i=@i+1
end
select id,count(1) con from @t group by id order by id
/*
--随机产生100个数,及每个数的统计
id Con
-----------
1 4
2 4
3 4
4 2
6 3
7 1
8 4
9 2
10 3
11 6
12 6
13 3
14 6
15 4
16 3
17 2
18 4
19 3
20 3
21 2
22 7
23 5
24 3
26 4
27 2
28 2
30 6
31 2
*/
/*----
01
23(2 行受影响)
*/
declare @t table (id int)
set @i=0
while @i <100
begin
insert @t select floor((rand()+0.1)*31)-2
set @i=@i+1
end
select id,count(1) con from @t group by id order by id
/*
--随机产生100个数,及每个数的统计
id Con
-----------
1 4
2 4
3 4
4 2
6 3
7 1
8 4
9 2
10 3
11 6
12 6
13 3
14 6
15 4
16 3
17 2
18 4
19 3
20 3
21 2
22 7
23 5
24 3
26 4
27 2
28 2
30 6
31 2
*/