Normal 0 7.8 磅 0 2 false false false EN-US ZH-CN X-NONE在Oracle中模擬測試資料是非常簡單的一件事情。

Oracle首先提供了一個dual的虛表

其次提供了一個Connect by語句，實現了虛表資料列的模擬

最後Oracle提供了強大的DBMS_RANDOM包進行相關隨機數的產生。

SELECT

TRUNC(DBMS_RANDOM.VALUE(1,101)),

DBMS_RANDOM.string('~',5),

DBMS_RANDOM.string('l',5),

DBMS_RANDOM.string('L',5),

DBMS_RANDOM.string('a',5),

DBMS_RANDOM.string('A',5),

DBMS_RANDOM.string('u',5),

DBMS_RANDOM.string('U',5),

DBMS_RANDOM.string('x',5),

DBMS_RANDOM.string('X',5),

DBMS_RANDOM.string('p',5),

DBMS_RANDOM.string('P',5)

from

(

SELECT level,ROWNUM rn

FROM DUAL

CONNECT BY ROWNUM<=1001

)

相比而言，SQLServer則沒那麼幸運了，首先沒有虛擬的概念，則需要構建一個物理表以儲存需要模擬的次數，再次需要使用CTE遞迴來模擬一個虛表資料，最後才透過相關隨機函式進行構建資料。

--建立一個物理表，並插入要模擬的次數，最大不能超過32767

CREATE TABLE RandTable

(

MaxNumber INT CHECK (MaxNumber >= 0 AND MaxNumber<=32767),

)

INSERT INTO RandTable values(32767);

--使用CTE遞迴構建列資料

WITH AutoSequence(MaxNumber,Identiy)

AS

(

SELECT e.MaxNumber,1 AS Identiy FROM RandTable AS e

UNION ALL

SELECT e.MaxNumber,Identiy+1 c FROM RandTable AS e,AutoSequence d

WHERE d.Identiy

)

--用時間+遞增值做種子進行RAND

SELECT LEFT(NEWID(),4),

RAND((DATEPART(mm,GETDATE())*100000)+(DATEPART(ss,GETDATE())*1000)+DATEPART(ms,GETDATE())),

RAND(Identiy+(DATEPART(mm,GETDATE())*100000)+(DATEPART(ss,GETDATE())*1000)+DATEPART(ms,GETDATE())),

CAST(RIGHT(CAST(RAND(Identiy+CAST(GETDATE() AS INT)) AS VARCHAR(100)),2) AS INT),

CAST(RIGHT(CAST(RAND(Identiy+(DATEPART(mm,GETDATE())*100000)+(DATEPART(ss,GETDATE())*1000)+DATEPART(ms,GETDATE())) AS VARCHAR(100)),2) AS INT)

FROM AutoSequence

OPTION (MAXRECURSION 32767);

SELECT LEFT(NEWID(),4),

CAST(RIGHT(CAST(RAND(Identiy+CAST(GETDATE() AS INT)) AS VARCHAR(100)),2) AS INT)

FROM AutoSequence

OPTION (MAXRECURSION 32767);

小結：

使用CTE遞迴和SQLServer隨機函式還是存在很多問題的。

1、一定需要構建一種物理表

2、CTE遞迴限制在0 到 32,767 之間

3、RAND產生的隨機數比較集中，透過時間+遞增值的方式來實現的話，只能擷取後幾位，導致無法控制隨機值的區域。

4、NEWID()產生的隨機數為字元和數字混雜，也不能得到預期的效果

關於SQLServer2005的學習筆記——CTE遞迴和模擬測試資料

相關文章