[SQL]產生亂數值 RAND NEWID

2014-10-16

16870
0
SQL

SQL中要產生一個亂數值可以使用 RAND 函數，
那什麼時候要再加入 NewID 給它亂下去呢?

在SQL中要產生一個亂數值可以使用 RAND 函數。

中間的 Seed 參數如果沒有指定的話，SQL會隨機給一個值。

以下的SQL每次執行就會回傳0到1(不含)的值。


SELECT RAND() AS RN1;
SELECT RAND() AS RN2;

在MSDN上有看到「如何用亂數新增日期 ?」的問題!

所以要產生一個亂數的日期，就是起始日期 + RAND() * 區間，如下，


DECLARE @SDate DATETIME, @EDate DATETIME;
--起始日
SET @SDate = '2013/01/01';
--結束日
SET @EDate = '2013/12/31';
--產生一個@SDate到@EDate的隨機日期
SELECT DATEADD(day, RAND() * DATEDIFF(DAY, @SDate, @EDate), @SDate);

那網路上好像有人會用 RAND + NEWID 呢?

如果每次查詢只查一個隨機的日期，上面方式就可以了!

如果是跟資料有關係，就是資料有幾筆就產生幾筆的隨機日期，上面的方式就會有問題哦!

以下建立一個 TAB_RAND Table，並新增5筆資料，然後產生隨機的日期，


USE tempdb
GO

--drop table TAB_RAND
CREATE TABLE TAB_RAND
(c1 INT)
GO

INSERT INTO dbo.TAB_RAND( c1 ) VALUES  (1);
INSERT INTO dbo.TAB_RAND( c1 ) VALUES  (2);
INSERT INTO dbo.TAB_RAND( c1 ) VALUES  (3);
INSERT INTO dbo.TAB_RAND( c1 ) VALUES  (4);
INSERT INTO dbo.TAB_RAND( c1 ) VALUES  (5);
GO

DECLARE @SDate DATETIME, @EDate DATETIME;
SET @SDate = '2013/01/01';
SET @EDate = '2013/12/31';

SELECT c1
, DATEADD(day, RAND() * DATEDIFF(DAY, @SDate, @EDate), @SDate) AS RN
FROM dbo.TAB_RAND;

可以發現，上面每筆的日期都是相同的哦!

所以就需要搭配 NEWID 這個 function來幫忙了!

它會建立 uniqueidentifier 類型的唯一值，所以有時會看到如果要Select亂排序的話，

可以使用 ORDER BY NEWID() 哦!

所以再來就是把 NEWID 的值傳入 RAND 當作是 seed 的值。

但是 seed 只能吃 int，不能直接吃 uniqueidentifier ，會產生錯誤。

所以就要把 NEWID() 轉成 int ，方式可以將它轉成 VARBINARY 或是透過 CHECKSUM 函數處理，如下，


SELECT CAST(NEWID() AS VARBINARY) VBIN, CHECKSUM(NEWID()) CHSUM;

再來就可以把TAB_RAND的SQL加入 NEWID 來產生「每筆資料」都不同的隨機日期，如下，


DECLARE @SDate DATETIME, @EDate DATETIME;
--起始日
SET @SDate = '2013/01/01';
--結束日
SET @EDate = '2013/12/31';

SELECT c1
, DATEADD(day, RAND(CAST(NEWID() AS VARBINARY)) * DATEDIFF(DAY, @SDate, @EDate), @SDate) AS RN_VBIN
, DATEADD(day, RAND(CHECKSUM(NEWID())) * DATEDIFF(DAY, @SDate, @EDate), @SDate) AS RN_CHSUM
FROM dbo.TAB_RAND;