随机为每一行加个行号?select sno=(select count(1)+1 from (select *,newid() as tid from tb) t2 where tid<t1.tid), col1,col2,...,coln from (select *,newid() as tid from tb) t1 order by sno这效率貌似不高
也可以用临时表select *,sno=identity(int,1,1) into # from tb order by newid()select * from # order by snodrop table #
再提供 create view v_RAND as select re=stuff(rand(),1,2,'') goalter function f_RANDBH() returns varchar(50) as begin declare @bhlen int set @bhlen=18 --长度 declare @r varchar(50) if not(isnull(@BHLen,0) between 1 and 50) set @BHLen=10 lb_bh: select @r=re from v_RAND while len(@r)<@BHLen select @r=@r+re from v_RAND set @r=left(@r,@BHLen) if exists(select * from tb with(xlock,paglock) where bh=@r) goto lb_bh return(@r) end go select dbo.f_RANDBH(50) --位数自定,最长50 --28717932268756598246257824810881924833466287969484if OBJECT_ID('tb') is not null drop table tb CREATE TABLE tb( BH varchar(50) PRIMARY KEY DEFAULT dbo.f_RANDBH(), col int)insert tb(col) select '1' union all select '2' union all select '3' union all select '4'select * from tb/* BH col 05687060012621147682667991318240430642346925752116 2 40513682887835085371622114346506827930698368827862 4 93225207611122374334349586587204091830009171524409 3 99914455216184595182213501939577680323090326328828 1 */
select RAND(CHECKSUM(NEWID())) from tb
----------------结果----------------------------
/* ----------------------
0.723152361031656
0.160484717876467
0.771261595722306
0.375298450220022
0.0203323160252095
0.795553182980927
0.0101359270481781
0.497010851787122(8 行受影响)*/
select ABS(CHECKSUM(NEWID()))-----------
615848090(所影响的行数为 1 行)
select abs(CHECKSUM(NEWID())) from tb
----------------结果----------------------------
/*
-----------
2063005324
385171136
216937953
1074920898
1650344707
2097133148
1216021305
1652729165(8 行受影响)*/
select *,CHECKSUM(RAND()) from tb
还是要用rand吧。
select (RAND(CHECKSUM(NEWID())))*66 from tb --0-66
select (RAND(CHECKSUM(NEWID())))*100 from tb --0-100
2、select cast(round(rand(checksum(newid()))*@number,0) as varchar)
好象有点长,有没简单些的方法?
SELECT @BNUM=3,@ENUM=5
SELECT ABS(CHECKSUM(NEWID()))%(@ENUM-@BNUM+1)+@BNUM
生成1-8范围内的随机数:
select (rand()*8+1)
因为rand返回的是float,所以计算值不是整数了。当然还要是可以在查询中用的,不同行的值不一样的了。呵呵努力下,看有没效率高些的方法。
这是1到10的随机整数
select cast((rand()*999+1) as int)
/*-----------
842(1 行受影响)
*/
where type='p' and number between 0 and 100
order by newid()
number
-----------
67
33
28
21
55
8
50
69
72
68
23
43
61
35
60
90
31
19
54
62
83
47
16
92
75
73
80
42
18
34
22
5
41
89
96
26
58
70
32
66
24
86
0
81
78
51
46
49
27
38
36
84
40
91
25
44
17
56
29
95
9
37
13
77
2
48
39
14
88
45
64
30
85
4
1
59
6
97
52
15
11
76
98
3
53
74
94
82
71
100
10
20
7
65
99
57
79
93
12
63
87(101 行受影响)
sno=(select count(1)+1 from (select *,newid() as tid from tb) t2 where tid<t1.tid),
col1,col2,...,coln
from
(select *,newid() as tid from tb) t1
order by sno这效率貌似不高
create view v_RAND
as
select re=stuff(rand(),1,2,'')
goalter function f_RANDBH()
returns varchar(50)
as
begin
declare @bhlen int
set @bhlen=18 --长度
declare @r varchar(50)
if not(isnull(@BHLen,0) between 1 and 50)
set @BHLen=10
lb_bh:
select @r=re from v_RAND
while len(@r)<@BHLen
select @r=@r+re from v_RAND
set @r=left(@r,@BHLen)
if exists(select * from tb with(xlock,paglock) where bh=@r)
goto lb_bh return(@r)
end
go
select dbo.f_RANDBH(50) --位数自定,最长50 --28717932268756598246257824810881924833466287969484if OBJECT_ID('tb') is not null drop table tb
CREATE TABLE tb(
BH varchar(50) PRIMARY KEY DEFAULT dbo.f_RANDBH(),
col int)insert tb(col) select '1'
union all select '2'
union all select '3'
union all select '4'select * from tb/*
BH col
05687060012621147682667991318240430642346925752116 2
40513682887835085371622114346506827930698368827862 4
93225207611122374334349586587204091830009171524409 3
99914455216184595182213501939577680323090326328828 1
*/