快速生成一个从1到1000000数据的序列表
USE master;
GO
IF OBJECT_ID('dbo.Nums', 'U') IS NOT NULL
DROP TABLE dbo.Nums;
CREATE TABLE dbo.Nums(n INT NOT NULL PRIMARY KEY);
DECLARE @max AS INT, @rc AS INT;
SET @max = 1000000;
SET @rc = 1;
INSERT INTO dbo.Nums(n) VALUES(1);
WHILE @rc * 2 <= @max
BEGIN
INSERT INTO dbo.Nums(n) SELECT n + @rc FROM dbo.Nums;
SET @rc = @rc * 2;
END
INSERT INTO dbo.Nums(n)
SELECT n + @rc FROM dbo.Nums WHERE n + @rc <= @max;
GO
利用上面的表,生成一个从20050101至20081231的测试数据的表(包含部分2004年12月数据)
DECLARE
@numorders AS INT,
@numcusts AS INT,
@numemps AS INT,
@numshippers AS INT,
@numyears AS INT,
@startdate AS DATETIME;
SELECT
@numorders = 1000000,
@numcusts = 20000,
@numemps = 500,
@numshippers = 5,
@numyears = 4,
@startdate = '20050101';
IF OBJECT_ID('dbo.Orders', 'U') IS NOT NULL
DROP TABLE dbo.Orders;
CREATE TABLE dbo.Orders
(
orderid INT NOT NULL,
custid CHAR(11) NOT NULL,
empid INT NOT NULL,
shipperid VARCHAR(5) NOT NULL,
orderdate DATETIME NOT NULL,
filler CHAR(155) NOT NULL DEFAULT('a')
);
INSERT INTO dbo.Orders(orderid, custid, empid, shipperid, orderdate)
SELECT n AS orderid,
'C' + RIGHT('000000000'
+ CAST(
1 + ABS(CHECKSUM(NEWID())) % @numcusts
AS VARCHAR(10)), 10) AS custid,
1 + ABS(CHECKSUM(NEWID())) % @numemps AS empid,
CHAR(ASCII('A') - 2
+ 2 * (1 + ABS(CHECKSUM(NEWID())) % @numshippers)) AS shipperid,
DATEADD(day, n / (@numorders / (@numyears * 365.25)), @startdate)
- CASE WHEN n % 10 = 0
THEN 1 + ABS(CHECKSUM(NEWID())) % 30
ELSE 0
END AS orderdate
FROM master.dbo.Nums
WHERE n <= @numorders
ORDER BY CHECKSUM(NEWID());
DATEADD(day, n / (@numorders / (@numyears * 365.25)), @startdate)
- CASE WHEN n % 10 = 0
THEN 1 + ABS(CHECKSUM(NEWID())) % 30
ELSE 0
END
DATEADD的第二个参数会忽略掉小数点后面的数,DATEADD(day,0.1,'20050101')和DATEADD(day,0.9,'20050101')的结果一样是2005-01-01,所以上面有多条相同日期的记录,
@numorders / (@numyears * 365.25) 为每个相同日期的记录数1000000 / (4 * 365.25) = 684
即20050101有684条记录,20050102有684条记录,以此类推
NEWID 返回的是uniqueidentifier类型的唯一值
CHECKSUM 返回按照表的某一行或一组表达式计算出来的校验和值,结果可为负也可为正
ABS 返回指定数值表达式的绝对值(正值)
例如执行下面语句:
DECLARE @s AS uniqueidentifier
SET @s = NEWID()
select @s,CHECKSUM(@s),ABS(CHECKSUM(@s))
结果为:
------------------------------------ ----------- -----------
D336A32E-C211-4B2B-8F3B-008314D40024 -1959770923 1959770923
ABS(CHECKSUM(NEWID())) % 30 返回0-29范围的随机数
1 + ABS(CHECKSUM(NEWID())) % 30 即返回1-30范围内的随机数
- CASE WHEN n % 10 = 0
THEN 1 + ABS(CHECKSUM(NEWID())) % 30
ELSE 0
END
则是当n % 10 = 0时,减去1至30内的范围数,所以表dbo.Orders除了20050101至20081231范围的数据,还有一小部分2004年12月的数据。
由于满足n % 10 = 0条件的数据684/10=68,因此>20050101的相同日期的记录数在684加减68即616-752范围内,可查询
select orderdate, COUNT(*) from dbo.Orders
group by orderdate