--自定义函数:取得指定上下限的随机数if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_rand]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_rand]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[v_rand]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[v_rand]
GO--需要这样一个视图
create view v_rand as select re=rand()
go--自定义函数:取得指定上下限的随机数
create function f_rand(
@下限 decimal(38,0),
@上限 decimal(38,0)
)
returns decimal(38,0)
as
begin
declare @r decimal(38,0)
select @r=cast(re*(@上限-@下限)+@下限 as decimal(38,0)) from v_rand
return(@r)
end
go--调用示例
select dbo.f_rand(10000000000000000000,900000000000000000000)
drop function [dbo].[f_rand]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[v_rand]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[v_rand]
GO--需要这样一个视图
create view v_rand as select re=rand()
go--自定义函数:取得指定上下限的随机数
create function f_rand(
@下限 decimal(38,0),
@上限 decimal(38,0)
)
returns decimal(38,0)
as
begin
declare @r decimal(38,0)
select @r=cast(re*(@上限-@下限)+@下限 as decimal(38,0)) from v_rand
return(@r)
end
go--调用示例
select dbo.f_rand(10000000000000000000,900000000000000000000)
select dbo.f_rand(10000000000000000000,999999999999999999999)
21位的数字超出了bigint,用字符返回行不行?
--自定义函数:取得指定上下限的随机数if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_rand]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_rand]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[v_rand]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[v_rand]
GO--需要这样一个视图
create view v_rand as select re=rand()
go--自定义函数:取得指定上下限的随机数
create function f_rand(
@下限 decimal(38,0),
@上限 decimal(38,0)
)
returns decimal(38,0)
as
begin
declare @r decimal(38,0)
select @r=cast(re*(@上限-@下限)+@下限 as decimal(38,0)) from v_rand
return(@r)
end
go
--测试的表
create table test(
字段 decimal(21,0) constraint u_字段_表 unique --创建唯一约束
)--插入10万条数据
declare @i int
set @i=0
while @i<100000
begin
insert test
select dbo.f_rand(10000000000000000000,999999999999999999999)
from(
select id=0 union all select 1
union all select id=2 union all select 3
union all select id=4 union all select 5
union all select id=6 union all select 7
union all select id=8 union all select 9
) a,(
select id=0 union all select 10
union all select id=20 union all select 30
union all select id=40 union all select 50
union all select id=60 union all select 70
union all select id=80 union all select 90
) b
set @i=@i+@@rowcount
end
goselect * from test
godrop table test
但说有的数至少千位以下都为0
修改一下
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_rand]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_rand]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[v_rand]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[v_rand]
GO
--需要这样一个视图
create view v_rand as select re= cast(rand() as dec(21,21))*0.999999999999999999999
go
--自定义函数:取得指定上下限的随机数
create function f_rand(
@下限 decimal(38,0),
@上限 decimal(38,0)
)
returns decimal(38,0)
as
begin
declare @r decimal(38,0)
select @r=cast(re *(@上限-@下限)+@下限 as decimal(38,0)) from v_rand
return(@r)
end
select dbo.f_rand(10000000000000000000,999999999999999999999)create table test(
字段 decimal(21,0) constraint u_字段_表 unique --创建唯一约束
)--插入10万条数据
set nocount ON
declare @i int
set @i=0
while @i<100000
begin
insert test
select dbo.f_rand(10000000000000000000,999999999999999999999)
from(
select id=0 union all select 1
union all select id=2 union all select 3
union all select id=4 union all select 5
union all select id=6 union all select 7
union all select id=8 union all select 9
) a,(
select id=0 union all select 10
union all select id=20 union all select 30
union all select id=40 union all select 50
union all select id=60 union all select 70
union all select id=80 union all select 90
) b
set @i=@i+@@rowcount
end
go
set nocount offselect * from test
godrop table test
楼上得兄弟能否留下MSN和QQ 方便交流
我是写asp.jsp+java+sql server的
字段中的value 后面的4位数字都是0
如何改变 这一情况!