22,210
社区成员
发帖
与我相关
我的任务
分享
create view v_RAND
as
select re=stuff(rand(),1,2,'')
go
alter 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 --28717932268756598246257824810881924833466287969484
if 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 *,sno=identity(int,1,1) into # from tb order by newid()
select * from # order by sno
drop table #
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 number from master..spt_values
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 行受影响)
1-1000
select cast((rand()*999+1) as int)
/*-----------
842
(1 行受影响)
*/
SELECT CEILING(RAND()*100/10)
这是1到10的随机整数
NEWID 对每台计算机返回的值各不相同。所显示的数字仅起解释说明的作用。
随机函数:rand()
在查询分析器中执行:select rand(),可以看到结果会是类似于这样的随机小数:0.36361513486289558,像这样的小数在实际应用中用得不多,一般要取随机数都会取随机整数。那就看下面的两种随机取整数的方法:
1、
A:select floor(rand()*N) ---生成的数是这样的:12.0
B:select cast( floor(rand()*N) as int) ---生成的数是这样的:12
2、
A:select ceiling(rand() * N) ---生成的数是这样的:12.0
B:select cast(ceiling(rand() * N) as int) ---生成的数是这样的:12
其中里面的N是一个你指定的整数,如100,可以看出,两种方法的A方法是带有.0这个的小数的,而B方法就是真正的整数了。
select rid=abs(checksum(newid()))%1000 from sysobjects
生成1-8范围内的随机数:
select (rand()*8+1)
SELECT CAST(NEWID() AS VARBINARY)*1