--首先创建下面的函数--自定义函数:取得指定上下限的随机数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(
@下限 int,
@上限 int
)
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(10,100),dbo.f_rand(10,100)
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(
@下限 int,
@上限 int
)
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(10,100),dbo.f_rand(10,100)
解决方案 »
- 一个简单的SQL题,可惜我不会??
- 急!如何将Oracle中decode的语句转化为Sql里面的语句??解决马上揭帖
- 菜鸟求救啊!SQL设置trigger的问题
- 高手们,请教工时计算问题
- 我的2000光盘里有如下几个文件夹,我想装开发版,但不知道在哪个文件夹里?
- sql server连接其他服务器出现超时的疑问
- 字段类型问题
- 自认为是MsSql高手的进来,最尖端的问题?1000大洋相送!!!!
- ,为什么SQL SERVER数据库中的NVACHAR字段在DELPHI中用ODBC连接时无法显示该字段
- 如何区分系统表和用户表?
- 如何在存储过程中使用不在同一个服务器上的不同数据库的两个表!急
- 谁来帮我创建一个表???
create table #t(id int identity(1,1),随机数 int constraint aa unique)select @rowcount=1000 --生成10000条记录
,@i=@rowcountinsert #t select distinct dbo.f_rand(1,@rowcount) from syscolumns
set @i=@i-@@rowcount
while @i>0
begin
insert #t select distinct dbo.f_rand(1,@rowcount) from sysobjects
set @i=@i-@@rowcount
end--显示生成的结果
select * from #tdrop table #t
select id from (
select id=a.id+b.id*10+c.id*100+d.id*1000+e.id*10000+1 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 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
) b,(
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
) c,(
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
) d,(
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
) e
) aa
-- where id <= 最大值 --如果需要上限,加上此条件即可
order by newid()
方法是新增一个临时字段,给这个临时字段赋rand(),然后按照这个字段排序
然后从1开始递增赋值