--取得随机数的视图 CREATE VIEW v_RAND AS SELECT re=STUFF(RAND(),1,2,'') GO--生成随机编号的函数 CREATE FUNCTION f_RANDBH(@BHLen int) RETURNS varchar(50) AS BEGIN DECLARE @r varchar(50) IF NOT(ISNULL(@BHLen,0) BETWEEN 1 AND 50) SET @BHLen=10lb_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--创建引用生成随机编号的函数 CREATE TABLE tb( BH char(10) PRIMARY KEY DEFAULT dbo.f_RANDBH(10), col int)--插放数据 BEGIN TRAN INSERT tb(col) VALUES(1) INSERT tb(col) VALUES(2) INSERT tb(col) VALUES(3) COMMIT TRAN SELECT * FROM tb GO /*--结果 BH col ------------------------------ ----------- 6128177354 1 7378536177 3 8387186129 2 --*/
select A=(checksum(newid())%(88-35)/2+(88-35))/100.0/* A --------------------------------------- 0.580000(1 row(s) affected) */
declare @i float set @i=rand() select @i=case when @i<0.35 then @i+0.35 when @i>0.88 then @i-0.88+0.35 else @i end select @i
CREATE VIEW v_RAND
AS
SELECT re=STUFF(RAND(),1,2,'')
GO--生成随机编号的函数
CREATE FUNCTION f_RANDBH(@BHLen int)
RETURNS varchar(50)
AS
BEGIN
DECLARE @r varchar(50)
IF NOT(ISNULL(@BHLen,0) BETWEEN 1 AND 50)
SET @BHLen=10lb_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--创建引用生成随机编号的函数
CREATE TABLE tb(
BH char(10) PRIMARY KEY DEFAULT dbo.f_RANDBH(10),
col int)--插放数据
BEGIN TRAN
INSERT tb(col) VALUES(1)
INSERT tb(col) VALUES(2)
INSERT tb(col) VALUES(3)
COMMIT TRAN
SELECT * FROM tb
GO
/*--结果
BH col
------------------------------ -----------
6128177354 1
7378536177 3
8387186129 2
--*/
A
---------------------------------------
0.580000(1 row(s) affected)
*/
declare @i float
set @i=rand()
select @i=case when @i<0.35 then @i+0.35 when @i>0.88 then @i-0.88+0.35 else @i end
select @i
波波
等 级:
发表于:2008-01-31 17:10:396楼 得分:0
SELECT 0.35+0.53*rand()
================
刚试验了一下,好像应该这样SELECT 0.35+(0.53-0.35)*rand()
declare @na int, @nb int
select @a=0.35, @b=0.88
select @na=@a*10000, @nb=@b*10000select A= cast((checksum(newid())%((@nb-@na)/2)+(@nb+@na)/2)/10000.0 as decimal(5,2))/*
A
---------------------------------------
0.63(1 row(s) affected)
*/
波波
等 级:
发表于:2008-01-31 17:10:396楼 得分:0
SELECT 0.35+0.53*rand()
================
刚试验了一下,好像应该这样 SELECT 0.35+(0.53-0.35)*rand()
=================
起始位置 + 距离乘以(0,1)的数字0.53 = 0.88 - 0.35楼主是不是看错了一点点啊
将0-1之间的随机数按0.35-0.88的区间倍数转换一下就行
生成0-1的随机数,假设为@val
0.35+@val(0.88-0.35) 即可得其之间的随机数