您的位置：首页 > 数据库 > mssqlserver  >  SqlServer生成连续数字根据指定的数字操作

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

 更新时间：2020年12月8日 11:31  点击：2236

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

一、Oracle使用ROWNUM实现方式

SELECT 
 ROWNUM number_list
FROM 表名
WHERE ROWNUM <= 10;

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

1.使用MASTER…spt_values方式

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

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写起来方便，可事先创建一个自定义函数，用来生成区间随机数：

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

说明

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

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

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

以上这篇SqlServer生成连续数字根据指定的数字操作就是小编分享给大家的全部内容了，希望能给大家一个参考，也希望大家多多支持猪先飞。

[!--infotagslink--]

相关文章

  • 金额阿拉伯数字转换为中文的存储过程

    Create Procedure AtoC @ChangeMoney Money as Set Nocount ON Declare @String1 char(20) Declare @String2 char(30) ...2016-11-25

  • c#中判断字符串是不是数字或字母的方法

    这篇文章介绍了C#判断字符串是否数字或字母的实例，有需要的朋友可以参考一下...2020-06-25

  • 金额阿拉伯数字转换为中文的自定义函数

    CREATE FUNCTION ChangeBigSmall (@ChangeMoney money) RETURNS VarChar(100) AS BEGIN Declare @String1 char(20) Declare @String2 char...2016-11-25

  • C#实现猜数字游戏

    这篇文章主要为大家详细介绍了C#实现猜数字游戏，文中示例代码介绍的非常详细，具有一定的参考价值，感兴趣的小伙伴们可以参考一下...2020-06-25

  • 安卓开发之设置密码只能输入字母和数字的组合

    设置登录密码我们一般会有限制的如由什么组合了，下面我们来看一篇关于安卓开发之设置密码只能输入字母和数字的组合方法，具体的细节如下所示。 无论是电脑还是手机...2016-09-20

  • SQL Server中row_number函数的常见用法示例详解

    这篇文章主要给大家介绍了关于SQL Server中row_number函数的常见用法，文中通过示例代码介绍的非常详细，对大家的学习或者工作具有一定的参考学习价值，需要的朋友们下面随着小编来一起学习学习吧...2020-12-08

  • php导出csv格式数据并将数字转换成文本的思路以及代码分享

    php导出csv格式数据实现：先定义一个字符串 存储内容，例如 $exportdata = '规则111,规则222,审222,规222,服2222,规则1,规则2,规则3,匹配字符,设置时间,有效期'."/n";然后对需要保存csv的数组进行foreach循环，例如复制代...2014-06-07

  • C#判断一个字符串是否是数字或者含有某个数字的方法

    这篇文章主要介绍了C#判断一个字符串是否是数字或者含有某个数字的方法，本文给大家介绍的非常详细，具有一定的参考借鉴价值,需要的朋友可以参考下...2020-06-25

  • C#查询SqlServer数据库并返回单个值的方法

    这篇文章主要介绍了C#查询SqlServer数据库并返回单个值的方法,涉及C#操作SQLServer数据库查询的相关技巧,需要的朋友可以参考下...2020-06-25

  • php生成唯一数字id的方法汇总

    关于生成唯一数字ID的问题，是不是需要使用rand生成一个随机数，然后去数据库查询是否有这个数呢？感觉这样的话有点费时间，有没有其他方法呢？当然不是，其实有两种方法可以解决。 1. 如果你只用php而不用数据库的话，那时间戳+随...2015-11-24

  • C#将数字转换成字节数组的方法

    这篇文章主要介绍了C#将数字转换成字节数组的方法,涉及C#字符串操作的技巧,非常具有实用价值,需要的朋友可以参考下...2020-06-25

  • SQLServer 错误: 15404，无法获取有关 Windows NT 组/用户 WIN-8IVSNAQS8T7\Administrator 的信息

    SQLServer 错误: 15404，无法获取有关 Windows NT 组/用户 'WIN-8IVSNAQS8T7\Administrator' 的信息，错误代码 0x534。 [SQLSTATE 42000] (ConnIsLoginSysAdmin)...2021-07-15

  • php数字每三位加逗号的功能函数

    php实现数字格式化，数字每三位加逗号的功能函数，具体代码如下： function num_format($num){ if(!is_numeric($num)){ return false; } $num = explode('.',$num);//把整数和小数分开 $rl = $num[1];//小数部分的值...2015-10-23

  • JS实现图片的不间断连续滚动的简单实例

    下面小编就为大家带来一篇JS实现图片的不间断连续滚动的简单实例。小编觉得挺不错的，现在就分享给大家，也给大家做个参考。一起跟随小编过来看看吧...2016-06-12

  • SQL SERVER迁移之更换磁盘文件夹的完整步骤

    这篇文章主要给大家介绍了关于SQL SERVER迁移之更换磁盘文件夹的完整步骤，文中通过图文介绍的非常详细，对大家的学习或者工作具有一定的参考学习价值，需要的朋友们下面随着小编来一起学习学习吧...2020-12-08

  • CentOS8安装SQLServer2019的过程

    这篇文章主要介绍了CentOS8安装SQLServer2019的步骤，本文通过命令实例相结合给大家介绍的非常详细，对大家的学习或工作具有一定的参考借鉴价值,需要的朋友可以参考下...2020-07-11

  • C++实现数字转换为十六进制字符串的方法

    这篇文章主要介绍了C++实现数字转换为十六进制字符串的方法,涉及C++操作数字与字符串转换的相关技巧,需要的朋友可以参考下...2020-04-25

  • SqlServer 数据库 三大  范式

    本文将基于三大范式原则，结合具体的实例做简要分析，对SqlServer 数据库 三大 范式相关知识感兴趣的朋友一起看看吧...2020-07-11

  • Lua判断变量是否为数字、字符串是否可以转换为数字等

    这篇文章主要介绍了Lua判断变量是否为数字、字符串是否可以转换为数字等,本文讲解了Lua 判断是字符还是数字的方法、Lua判断数字的方法、判断可否转换为数字的方法、判断并且准备一个初值的方法,需要的朋友可以参考下...2020-06-30

  • php 判断是否是中文/英文/数字示例代码

    复制代码 代码如下: $str='asb天水市12'; if (preg_match("/^[/x7f-/xff]+$/", $str)){ echo '全部是汉字'; }else { echo '不全是汉字'; } /** PHP自带的判断是否是中文， eregi('[^/x00-/x7F]', $str ) //中文 ereg...2013-10-04