SqlServer生成连续数字根据指定的数字操作_Sql Server

SqlServer生成连续数字根据指定的数字操作

2021-01-18 14:54天河~ Sql Server

这篇文章主要介绍了SqlServer生成连续数字根据指定的数字操作，具有很好的参考价值，希望对大家有所帮助。一起跟随小编过来看看吧

需求是使用sqlserver根据指定的数字和表生成一串连续的数字，类似于oracle中ROWNUM的功能，具体实现如下：

一、Oracle使用ROWNUM实现方式

									SELECT

									 ROWNUM number_list

									FROM 表名

									WHERE ROWNUM <= 10;

SqlServer生成连续数字根据指定的数字操作

二、SqlServer实现上述功能的三种方式

1.使用MASTER…spt_values方式

									SELECT

									 number 

									FROM

									 MASTER..spt_values 

									WHERE

									 TYPE = 'P'

									 AND number > 0 

									 AND number <= 10;

SqlServer生成连续数字根据指定的数字操作

spt_values是master数据库中的一张系统表，number的数值范围是0~2047

2.使用String_Split函数实现

select row_number() over (order by (select 1)) from String_Split(space(6),' ')

String_Split是SQLServer 2016的新函数，这个方法只对2016及后续版本有效。

（我的数据库版本是2008，此方式未验证）

3.使用top+ROW_NUMBER () OVER方式实现

									SELECT TOP

									 10 ROW_NUMBER () OVER (

									ORDER BY

									 (SELECT 1)) number_list 

									FROM

									(SELECT TOP 10 * FROM 表名) t;

SqlServer生成连续数字根据指定的数字操作

第一种方式对数据库版本无要求，但是取值范围有限制0~2047；

第二种方式对数据库版本有要求，要在2016及之上；

第三种方式对数据库版本无要求，只要表里的数据量大于要生成的数字即可；

补充知识：数据库生成测试数据（SQL实现）

需求

项目中偶尔会有造数据进行测试的情况，根据常见的数据特征，我这里假设数据某表Table_X含4个字段，每个字段的要求如下，需要造出5000条数据。

SqlServer生成连续数字根据指定的数字操作

以上需求看上去非常简单，但比较具有代表性，复杂需求也是由小需求排列组合而成。

功能准备

随机数

造数的核心功能是生成随机数，SQL Server下有RAND()系统函数可以生成0到1之间的小数，利用它可以生成固定区间 [Min,Max] 的小数：Min + (Max - Min) * RAND()，另外CHECKSUM(NEWID())也可以生成一串比较大的整数（9位或10位数居多），再配合ABS取绝对值和取模运算，就可以很好的控制所生成随机数的范围了。

区间随机数函数

为了SQL写起来方便，可事先创建一个自定义函数，用来生成区间随机数：

									CREATE VIEW vwRand

									AS

									SELECT RAND() AS RandValue

									GO

									CREATE FUNCTION dbo.Random_Range

									(

									  @Min DECIMAL(22,5)

									  ,@Max DECIMAL(22,5)

									)RETURNS DECIMAL(22,5)

									-- return value between @Min and @Max

									BEGIN

									  DECLARE @Result DECIMAL(22,5);

									  SELECT @Result = @Min + (@Max - @Min) * RandValue FROM vwRand;

									  RETURN @Result

									END

									GO

这里先创建了一个视图，然后在函数体内引用，是因为SQL Server不支持直接在函数体中引用RAND函数，会报错Invalid use of a side-effecting operator ‘rand' within a function.。

列表选择

如果是从少量的枚举数值中选择，可以使用CHOOSE函数。

若从大量候选项中选择，可将数据导入含自增列的数据库表后，通过标量子查询进行选择。

造数SQL

									SELECT TOP 5000

									  ABS(CHECKSUM(NEWID())) % 100 + 1 AS Col_A

									  ,dbo.Random_Range(5000, 10000) AS Col_B

									  ,ISNULL(CHOOSE(ABS(CHECKSUM(NEWID())) % 3 + 1, 'S', 'M', 'L', 'XL', 'XXL'), 'M') AS Col_C

									  ,DATEADD(DAY, dbo.Random_Range(0, DATEDIFF(DAY, '20000101', '20201231')), '20000101') AS Col_D

									FROM sys.all_columns