需求是使用sqlserver根據(jù)指定的數(shù)字和表生成一串連續(xù)的數(shù)字，類(lèi)似于oracle中ROWNUM的功能，具體實(shí)現(xiàn)如下：

一、Oracle使用ROWNUM實(shí)現(xiàn)方式

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

二、SqlServer實(shí)現(xiàn)上述功能的三種方式

1.使用MASTER…spt_values方式

SELECT
 number 
FROM
 MASTER..spt_values 
WHERE
 TYPE = 'P' 
 AND number > 0 
 AND number <= 10;

spt_values是master數(shù)據(jù)庫(kù)中的一張系統(tǒng)表，number的數(shù)值范圍是0~2047

2.使用String_Split函數(shù)實(shí)現(xiàn)

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

String_Split是SQLServer 2016的新函數(shù)，這個(gè)方法只對(duì)2016及后續(xù)版本有效。

（我的數(shù)據(jù)庫(kù)版本是2008，此方式未驗(yàn)證）

3.使用top+ROW_NUMBER () OVER方式實(shí)現(xiàn)

SELECT TOP
 10 ROW_NUMBER () OVER (
ORDER BY
 (SELECT 1)) number_list 
FROM
(SELECT TOP 10 * FROM 表名) t;

第一種方式對(duì)數(shù)據(jù)庫(kù)版本無(wú)要求，但是取值范圍有限制0~2047；

第二種方式對(duì)數(shù)據(jù)庫(kù)版本有要求，要在2016及之上；

第三種方式對(duì)數(shù)據(jù)庫(kù)版本無(wú)要求，只要表里的數(shù)據(jù)量大于要生成的數(shù)字即可；

補(bǔ)充知識(shí)：數(shù)據(jù)庫(kù)生成測(cè)試數(shù)據(jù)（SQL實(shí)現(xiàn)）

需求

項(xiàng)目中偶爾會(huì)有造數(shù)據(jù)進(jìn)行測(cè)試的情況，根據(jù)常見(jiàn)的數(shù)據(jù)特征，我這里假設(shè)數(shù)據(jù)某表Table_X含4個(gè)字段，每個(gè)字段的要求如下，需要造出5000條數(shù)據(jù)。

以上需求看上去非常簡(jiǎn)單，但比較具有代表性，復(fù)雜需求也是由小需求排列組合而成。

功能準(zhǔn)備

隨機(jī)數(shù)

造數(shù)的核心功能是生成隨機(jī)數(shù)，SQL Server下有RAND()系統(tǒng)函數(shù)可以生成0到1之間的小數(shù)，利用它可以生成固定區(qū)間 [Min,Max] 的小數(shù)：Min + (Max - Min) * RAND()，另外CHECKSUM(NEWID())也可以生成一串比較大的整數(shù)（9位或10位數(shù)居多），再配合ABS取絕對(duì)值和取模運(yùn)算，就可以很好的控制所生成隨機(jī)數(shù)的范圍了。

區(qū)間隨機(jī)數(shù)函數(shù)

為了SQL寫(xiě)起來(lái)方便，可事先創(chuàng)建一個(gè)自定義函數(shù)，用來(lái)生成區(qū)間隨機(jī)數(shù)：

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

這里先創(chuàng)建了一個(gè)視圖，然后在函數(shù)體內(nèi)引用，是因?yàn)镾QL Server不支持直接在函數(shù)體中引用RAND函數(shù)，會(huì)報(bào)錯(cuò)Invalid use of a side-effecting operator ‘rand' within a function.。

列表選擇

如果是從少量的枚舉數(shù)值中選擇，可以使用CHOOSE函數(shù)。

若從大量候選項(xiàng)中選擇，可將數(shù)據(jù)導(dǎo)入含自增列的數(shù)據(jù)庫(kù)表后，通過(guò)標(biāo)量子查詢(xún)進(jìn)行選擇。

造數(shù)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

說(shuō)明

實(shí)際需求可能字段非常多，但基本都可以用以上寫(xiě)法，修改參數(shù)即可；

SQL的功能畢竟有限，基本只適合從固定列表中隨機(jī)選擇，以及生成隨機(jī)數(shù)值性數(shù)據(jù)的場(chǎng)景；

若要造出更符合業(yè)務(wù)領(lǐng)域特性的數(shù)據(jù)，比如批量生成城市名、郵箱、人名、手機(jī)號(hào)、地址等數(shù)據(jù)，用純SQL就會(huì)有點(diǎn)吃力了，下一篇將介紹如何用python的faker庫(kù)生成測(cè)試數(shù)據(jù)。

以上這篇SqlServer生成連續(xù)數(shù)字根據(jù)指定的數(shù)字操作就是小編分享給大家的全部?jī)?nèi)容了，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。

最新評(píng)論