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

需求是使用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生成连续数字根据指定的数字操作就是小编分享给大家的全部内容了，希望能给大家一个参考，也希望大家多多支持我们。

赞 (0)

相关推荐

  • 简单实现SQLServer转MYSQL的方法

    简单实现SQLServer转MYSQL的方法

    本次转换需要依赖使用工具Navicat Premium. 首先,将数据库移至本地SQLServer,我试过直接在局域网上其他SQLServer服务器上想转到本地Mysql好像有问题,想将远程数据库备份恢复到本地. 1.打开Navicat Premium,新建一个同名的数据库,然后在表上点击"导入向导".并选择"ODBC". 2.选择SQLServer提供程序,并输入参数. 3.然后就拼命下一步.下一步. 在这里有个条件查询,如果有太多不想要的数据,或者字段类型有问题

  • SQLserver中的declare变量用法

    SQL中的declare用法 平时写SQL查询.存储过程都是凭着感觉来,没有探究过SQL的具体语法,一直都是按c#那一套往SQL上模仿,前几天项目中碰到一个问题引起了我对declare定义变量的作用域的兴趣. 大家都知道c#中的局部变量,在if中如果我们定义一个变量的话他的作用到if结束为止,if外是不识别这个变量的,else里都不能使用,简单的写一下. if(true) { Int32i=1; Console.WriteLine(i); } 这个i的作用域就是if里面,如果我们在if外面用这个

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

    需求是使用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 >

  • php生成随机字符串可指定纯数字、纯字母或者混合的

    php 生成随机字符串 可以指定是纯数字 还是纯字母 或者混合的. 可以指定长度的. 复制代码 代码如下: function rand_zifu($what,$number){ $string=''; for($i = 1; $i <= $number; $i++){ //混合 $panduan=1; if($what == 3){ if(rand(1,2)==1){ $what=1; }else{ $what=2; } $panduan=2; } //数字 if($what==1){ $str

  • sql server使用公用表表达式CTE通过递归方式编写通用函数自动生成连续数字和日期

    问题: 在数据库脚本开发中,有时需要生成一堆连续数字或者日期,例如yearly report就需要连续数字做年份,例如daily report就需要生成一定时间范围内的每一天日期. 而自带的系统表master..spt_values存在一定的局限性,只是从0到2047(验证脚本:select * from master..spt_values b where b.type = 'P'),也不能直接生成连续日期. 可能大部分人会想到一个笨办法,通过while循环去逐条插入数据到临时表,每次数字加1

  • Pytorch使用MNIST数据集实现CGAN和生成指定的数字方式

    CGAN的全拼是Conditional Generative Adversarial Networks,条件生成对抗网络,在初始GAN的基础上增加了图片的相应信息. 这里用传统的卷积方式实现CGAN. import torch from torch.utils.data import DataLoader from torchvision.datasets import MNIST from torchvision import transforms from torch import opti

  • js控制文本框只能输入中文、英文、数字与指定特殊符号的实现代码

    JS 控制文本框只能输入数字 <input onkeyup="value=value.replace(/[^0-9]/g,'')"onpaste="value=value.replace(/[^0-9]/g,'')" oncontextmenu ="value=value.replace(/[^0-9]/g,'')"> JS 控制文本框只能输入数字.小数点 <inputonkeyup="value=value.repl

  • c++连续输入未知个数的数字操作

    看代码吧~ vector<int> v; int num; while (cin >> num) { v.push_back(num); if (num == 0) break; } 方式一: 以空格或者回车结束每次输入,以固定输入数字0结束输入(也可以更改为其他字符) string test; getline(cin, test); int input_nums = 0; vector<int> indexes; indexes.push_back(0); for (

  • python 实现快速生成连续、随机字母列表

    0.摘要 本文介绍了生成连续和随机字母表的方法,用于快速生成大量字母数据. 主要使用chr()函数,将数字通过ASCII表转换为相应字母. 1.chr() 函数 chr() 用一个范围在 range(256)内的(就是0-255)整数作参数,返回一个对应的字符. 输入:可以是10进制也可以是16进制的形式的数字. print(chr(48), chr(49), chr(97)) # 十进制 #result:0 1 a print(chr(0x30), chr(0x31), chr(0x61))

  • PHP简单实现生成txt文件到指定目录的方法

    本文实例讲述了PHP简单实现生成txt文件到指定目录的方法.分享给大家供大家参考,具体如下: <?php //fopen第二个参数可以是以下四个,区别就是是清空内容再写还是在后面累加内容 //"w" 写入方式打开,将文件指针指向文件头并将文件大小截为零.如果文件不存在则尝试创建之. //"w+" 读写方式打开,将文件指针指向文件头并将文件大小截为零.如果文件不存在则尝试创建之. //"a" 写入方式打开,将文件指针指向文件末尾.如果文件不存

  • java 输入一个数字,反转输出这个数字的值(实现方法)

    如下所示: package 第四天; import java.util.Scanner; public class 数字反转 { public static void main(String[] args) { Scanner sc = new Scanner(System.in); System.out.println("请输入一个整数:"); int num=sc.nextInt(); int result=0;//存反转的数字 while(true) { int n=num%10

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

    第一种就是 最常见的 用Try..Catch.. 再try中强转你要确认的string 类型 成功就是int  catch 就不是 string a = "avdfd"; try { int b = int.Parse(a); } catch (Exception) { Console.WriteLine("不是"); } 还有就是简单一点的 int.Tryparse() 比如 bool IsNumber = int.TryParse("你要判断的值&qu

随机推荐