SQL Server 随机数,随机区间,随机抽取数据rand(),floor(),ceiling(),round(),newid()函数等

蓝晶灵

　　在查询分析器中执行：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方法就是真正的整数了。
    大致一看，这两种方法没什么区别，真的没区别？其实是有一点的，那就是他们的生成随机数的范围：
方法1的数字范围：0至N-1之间，如cast(floor(rand()*100) as int)就会生成0至99之间任一整数
方法2的数字范围：1至N之间，如cast(ceiling(rand() * 100) as int)就会生成1至100之间任一整数
对于这个区别，看SQL的联机帮助就知了：
------------------------------------------------------------------------------------
　　比较 CEILING 和 FLOOR
CEILING 函数返回大于或等于所给数字表达式的最小整数。FLOOR 函数返回小于或等于所给数字表达式的最大整数。例如，对于数字表达式 12.9273，CEILING 将返回 13，FLOOR 将返回 12。FLOOR 和 CEILING 返回值的数据类型都与输入的数字表达式的数据类型相同。
----------------------------------------------------------------------------------
现在，各位就可以根据自己需要使用这两种方法来取得随机数了^_^
　　另外，还要提示一下各位菜鸟，关于随机取得表中任意N条记录的方法，很简单，就用newid()：
select top N *  from table_name order by newid() ----N是一个你指定的整数，表是取得记录的条数
　　3,
　　ROUND() 函数
ROUND 函数用于把数值字段舍入为指定的小数位数。
SQL ROUND() 语法
SELECT ROUND(column_name,decimals) FROM table_name
　　参数描述
column_name必需。要舍入的字段。
decimals必需。规定要返回的小数位数。
　　例如：
　　select ROUND(15.258, 1)  结果是：15.300
　　可以利用ROUND() 函数与RAND()函数来随机生成指定区间：



DECLARE @Result INT
DECLARE @Upper INT
DECLARE @Lower INT
SET @Lower = 1
SET @Upper = 10
SELECT @Result = ROUND(((@Upper - @Lower -1) * RAND() + @Lower), 0)
SELECT @Result
　　以上代码得到一个[@Lower,@Upper)区间内的数，即最小是@Lower，最大是@Upper-1之间的随机数。
　　结合以上知识，写一个自动生成订单及配货清单的存储过程：



  1 USE [KaiFanDB]
  2 GO
  3
  4 /****** Object:  StoredProcedure [dbo].[ProcAutoGenerationOfOrders]    Script Date: 2016/8/9 13:42:51 ******/
  5 DROP PROCEDURE [dbo].[ProcAutoGenerationOfOrders]
  6 GO
  7
  8 /****** Object:  StoredProcedure [dbo].[ProcAutoGenerationOfOrders]    Script Date: 2016/8/9 13:42:51 ******/
  9 SET ANSI_NULLS ON
10 GO
11
12 SET QUOTED_IDENTIFIER ON
13 GO
14
15
16
17 -- =============================================
18 -- Author:        <熊学浩>
19 -- Create date: <2016-07-31>
20 -- Description:    <自动生成订单>
21 -- =============================================
22 CREATE PROCEDURE [dbo].[ProcAutoGenerationOfOrders]
23     -- Add the parameters for the stored procedure here
24 AS
25 BEGIN
26     -- SET NOCOUNT ON added to prevent extra result sets from
27     -- interfering with SELECT statements.
28     SET NOCOUNT ON;
29
30
31 declare @num int;
32 set @num=5;
33 while(@num>0)
34 begin
35     print('@num='+ CONVERT(VARCHAR,@num));
36     /* 生成订单开始 */
37     declare @oid char(14);
38     set @oid=CONVERT(VARCHAR,GETDATE(),112)+ CONVERT(VARCHAR,ROUND(((1000000 - 100000 -1) * RAND() + 100000), 0)); --生成14位纯数字订单号，例如“20160731666666”
39     if(exists(select * from [dbo].[Order] where [OID]=@oid))
40     begin
41         print ('@oid='+CONVERT(VARCHAR,@oid)+N',订单号已存在则跳过');
42     end
43     else
44     begin
45         print ('@oid='+CONVERT(VARCHAR,@oid)+N',订单号不存在则写入');
46
47         declare @uid int;
48         select top(1) @uid=UI.[UID] from [dbo].[Userinfo] as UI inner join [dbo].[UserAddress] as UA on UA.[UID]=UI.[UID]
49         where UI.[UID] not in (select top(10) [buyerUID] from [dbo].[Order] order by [AddTime] desc) --买家用户离散
50         order by NEWID(); --随机抽取数据
51         declare @bid int;
52         select top(1) @bid=[BID] from [dbo].[Businesses]
53         where [StatusCode]=1 and [StatusCode]=1
54             and [BID] in (select distinct [BID] from [dbo].[Food])
55             and [BID] not in (select top(2) [sellerBID] from [dbo].[Order] order by [AddTime] desc) --卖家店铺离散
56         order by NEWID(); --随机抽取数据
57
58         declare @totalprice int;
59         set @totalprice=0;
60         
61         declare @time datetime;
62         set @time=DATEADD(MI, -cast(floor(rand() * 30) as int), GETDATE());  --生成早于[1-30)分钟之间的时间，时间分散开使得订单更逼真
63
64         begin transaction;  --开始事务
65         declare @tran_error int;  --定义变量，累积事务执行过程中的错误
66         set @tran_error = 0;
67         print (N'开始事务');
68
69         /* 写入订单开始 */
70         declare @buyerName nvarchar(10),@buyerContactWay nvarchar(20),@buyerAddress nvarchar(50),@Sex tinyint,@Description nvarchar(50),@NeedTime nvarchar(20);
71         select top(1) @buyerName=[ContactName],@buyerContactWay=[MobilePhone],@buyerAddress=([Address]+ CONVERT(NVARCHAR,[House])),@Sex=[Sex] from [dbo].[UserAddress] where [UID]=@uid order by [isDefault] desc;
72         set @Description= null
73         set @NeedTime=N'尽快送达';
74
75         declare @sellerName nvarchar(10),@sellerContactWay nvarchar(20),@sellerAddress nvarchar(50),@sellerDescription nvarchar(50);
76         select top(1) @sellerName=[NickName],@sellerContactWay=(CONVERT(NVARCHAR,[Telephone])+CONVERT(NVARCHAR,[MobilePhone])),@sellerAddress=[Address],@sellerDescription=[ShortDescription] from [dbo].[Businesses] where [BID]=@bid order by [AddTime] desc;
77
78         INSERT INTO [dbo].[Order] VALUES(@oid
79             ,@uid,@buyerName,@buyerContactWay,@buyerAddress,@Description,@NeedTime
80             ,@bid,@sellerName,@sellerContactWay,@sellerAddress,@sellerDescription
81             ,@sellerName,@totalprice,1,@totalprice
82             ,null,null,1,0,null,@time,@Sex,0,0,null); --插入订单清单
83         ----- 执行语句
84         set @tran_error = @tran_error + @@error;  --累积错误
85
86         print (N'写入订单结束');
87         /* 写入订单结束 */
88
89         if(@tran_error < 1)
90         begin
91             /* 写入订单清单开始 */
92             declare @foodnumber int;
93             set @foodnumber=cast(ceiling(rand() * 5) as int);  --生成[1-5]之间的随机数
94             print (N'需要写入 '+CONVERT(VARCHAR,@foodnumber)+N' 条订单清单');
95             while(@foodnumber>0)
96             begin
97                 declare @fid int;
98                 declare @funitprice int;
99                 declare @fname nvarchar(12);
100                 select top(1) @fid=[FID],@fname=[Name],@funitprice=[UnitPrice] from [dbo].[Food] where [BID]=@bid order by NEWID(); --随机抽取数据
101
102                 declare @fcategoryname nvarchar(10);
103                 select top(1) @fcategoryname=FC.[CategoryName] from
104                     [dbo].[FoodCategoryRelation] as FCR
105                     inner join [dbo].[FoodCategory] as FC on FC.[FCID]=FCR.[FCID]
106                 where FCR.[FID]=@fid;
107            
108                 INSERT INTO [dbo].[OrderBill] VALUES(@oid,@fid,@fname,@funitprice,@fcategoryname,1,GETDATE()); --插入订单清单
109                 ----- 执行语句
110                 set @tran_error = @tran_error + @@error;  --累积错误
111
112                 set @totalprice=@totalprice + (@funitprice * 1);
113                 print (N'写入一条订单清单完成,@foodnumber='+CONVERT(NVARCHAR,@foodnumber)+ N',@totalprice='+CONVERT(NVARCHAR,@totalprice));
114                 
115                 set @foodnumber=@foodnumber-1;
116             end
117
118             print (N'写入订单清单结束,@foodnumber='+CONVERT(NVARCHAR,@foodnumber)+ N',@totalprice='+CONVERT(NVARCHAR,@totalprice));
119             /* 写入订单清单结束 */
120
121             if(@totalprice>0)
122             begin
123                 update [dbo].[Order] set [TotalPrice]=@totalprice,[PayPrice]=@totalprice,[PayMethod]=1,[PayTime]=null,[OrderStatus]=1 where [OID]=@oid;
124                 ----- 执行语句
125                 set @tran_error = @tran_error + @@error;  --累积错误
126
127                 print (N'修改订单结束');
128             end
129            
130         end
131
132
133         --判断要回滚或提交事物
134         if(@tran_error <> 0)  --有误
135             begin
136 　　　　    print (N'回滚事务');
137                 rollback  transaction;
138             end
139         else  
140             begin
141 　　　　    print (N'提交事务');
142                 commit transaction;
143                 set @num=@num-1;
144             end
145
146     end
147     /* 生成订单结束 */
148
149 end
150
151
152 END
153
154
155
156 GO