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

2020/11/17 12:17
阅读数 88

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

一、Oracle使用ROWNUM实现方式
1
2
3
4
SELECT
ROWNUM number_list
FROM 表名
WHERE ROWNUM <= 10;







二、SqlServer实现上述功能的三种方式
1.使用MASTER…spt_values方式

1
2
3
4
5
6
7
8
SELECT
number
FROM
MASTER..spt_values
WHERE
TYPE = 'P'
AND number > 0
AND number <= 10;














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方式实现

1
2
3
4
5
6
SELECT TOP
10 ROW_NUMBER () OVER (
ORDER BY
(SELECT 1)) number_list
FROM
(SELECT TOP 10 * FROM 表名) t;










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

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

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

补充知识:数据库生成测试数据(SQL实现)

需求

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

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

功能准备

随机数

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

区间随机数函数

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
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

1
2
3
4
5
6
SELECT TOP 5000
ABS(CHECKSUM(NEWID())) % 100 + 1 AS Col_A
,dbo.Random_Rangehttps://blog.51cto.com/14830013/2549657(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
说明











实际需求可能字段非常多,但基本都可以用以上写法,修改参数即可;

SQL的功能毕竟有限,基本只适合从固定列表中随机选择,以及生成随机数值性数据的场景;

若要造出更符合业务领域特性的数据,比如批量生成城市名、邮箱、人名、手机号、地址等数据,用纯SQL就会有点吃力了,下一篇将介绍如何用python的faker库生成测试数据。

以上这篇SqlServer生成连续数字根据指

展开阅读全文
打赏
0
0 收藏
分享
加载中
更多评论
打赏
0 评论
0 收藏
0
分享
返回顶部
顶部