Sql 语句小课堂7:在sqlserver对多行数据实施随机数 |
您所在的位置:网站首页 › sql随机生成1到100随机整数 › Sql 语句小课堂7:在sqlserver对多行数据实施随机数 |
Sql 语句小课堂7:在sqlserver对多行数据实施随机数
rand 函数newid 函数使用 apply模拟测试自定义函数尴尬,使用 checksum 函数
rand 函数
首先,sqlserver里提供了随机函数rand,但这东西是个大坑,每次单独使用没问题,但是在一个查询中出现多行结果时,这就是个坑了,因为rand的优先级比查询指令高!所以,结果就会如下图这样了 CSDN 文盲老顾的博客,https://blog.csdn.net/superwfei newid 函数然后,老顾想自己写个随机函数,使用rand和newid来结合,产生一个float值,结果,函数没办法写,sqlserver报错了 消息 443,级别 16,状态 1,过程 rnd,行 13 [批起始行 15] 在函数内对带副作用的运算符 ‘newid’ 的使用无效。 消息 443,级别 16,状态 1,过程 rnd,行 25 [批起始行 15] 在函数内对带副作用的运算符 ‘rand’ 的使用无效。
但是没关系,不能直接用rand,但用cross结合newid和rand,一样来实现随机值 select number,rnd.* from master..spt_values cross apply( select rnd from ( select convert(varchar(50),newid()) guid ) a cross apply ( select rand( convert( bigint ,convert( varbinary ,cast(N'0x' + substring(guid,0,charindex('-',guid)) as char) ,1 ) )%2147483647 ) rnd ) b ) rnd where type='p'
需要注意各个类型转换不能缺少,newid()得到的类型是uniqueidentifier,不能使用字符串处理函数,需要先转成字符串才能截取减号前的内容 另外就是,转成十进制数字时,有时会超出int的取值范围,所以,我在这里用取余来得到一个确定的int类型的值,毕竟rand函数不支持bigint作为参数 模拟测试那么,既然可以实现了各行的随机值不相同了,那么,咱们就用一个模拟需求来看看效果如何 先描述一下需求: 对0到2047这2000多个数字进行随机分布,分布到连续的30天中,早8点至晚10点的时间段内 因为有两个分布内容,一个是日期段,一个是时间段,所以我们使用两个cross来取两个随机数 select number,日期.*,时间.* from master..spt_values cross apply( select rnd from ( select convert(varchar(50),newid()) guid ) a cross apply ( select rand( convert( bigint ,convert( varbinary ,cast(N'0x' + substring(guid,0,charindex('-',guid)) as char) ,1 ) )%2147483647 ) rnd ) b ) 日期 cross apply( select rnd from ( select convert(varchar(50),newid()) guid ) a cross apply ( select rand( convert( bigint ,convert( varbinary ,cast(N'0x' + substring(guid,0,charindex('-',guid)) as char) ,1 ) )%2147483647 ) rnd ) b ) 时间 where type='p'
由于上边这个写法太复杂了,所以做一个newid到rand之间的中间函数 Create FUNCTION [dbo].[GuidToInt] ( @guid uniqueidentifier ) RETURNS int AS BEGIN DECLARE @r int,@s varchar(50) select @s = convert(varchar(50),@guid) select @r = convert( bigint ,convert( varbinary ,cast(N'0x' + substring(@s,0,charindex('-',@s)) as char) ,1 ) )%2147483647 RETURN @r END然后,我们的指令就可以变形一下了 select number,日期.*,x.* from master..spt_values cross apply( select rand(master.dbo.GuidToInt(newid())) rnd1 ,rand(master.dbo.GuidToInt(newid())) rnd2 ) 日期 cross apply ( select dateadd(ms,rnd1*60*60*13000,dateadd(hour,8,dateadd(d,floor(rnd2*31),'2022-7-29'))) d ) x where type='p' order by d
写完之后,才发现,还有一个checksum 函数,用来计算校验和,可以用作中间函数,即 select rand(checksum(newid())) --即可得到适用的随机数了现在好尴尬啊。。。。 |
今日新闻 |
推荐新闻 |
CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |