【题目】有兴趣和空闲时间的同学来试试

[飞扬的尘埃]

完成下列步骤：
1、生成一百万个（0,1000]的随机数，存入一张新建表中。
2、从以上随机数中，查询出99有多少个。

要求：
1、运算时间很重要。
2、尽量使用常见的数据库。
3、编写脚本完成所有步骤。

以下是在SQL Server 2005 SP3中做的：

IF EXISTS(SELECT 1 FROM sys.objects WHERE [NAME] = 'NUMBERS')
	DROP TABLE NUMBERS
GO
CREATE TABLE NUMBERS(
	NUM INT
)
GO
DECLARE @i INT
SET @i = 0
WHILE @i < 1000000
BEGIN
	INSERT INTO NUMBERS VALUES(CAST(RAND()*1000 AS INT))
	SET @i = @i + 1
END
SELECT X99=COUNT(NUM) FROM NUMBERS GROUP BY NUM HAVING NUM=99
------用时25秒-------------------------------------------------------

IF EXISTS(SELECT 1 FROM sys.objects WHERE [NAME] = 'NUMBERS')
	DROP TABLE NUMBERS
GO
CREATE TABLE NUMBERS(
	NUMA INT,
	NUMB INT
)
GO
DECLARE @i INT
SET @i = 0
WHILE @i < 500000
BEGIN
	INSERT INTO NUMBERS VALUES(CAST(RAND()*1000 AS INT),CAST(RAND()*1000 AS INT))
	SET @i = @i + 1
END
SELECT X99=SUM(X99) FROM (
SELECT X99=COUNT(NUMA) FROM NUMBERS GROUP BY NUMA HAVING NUMA=99
UNION ALL
SELECT X99=COUNT(NUMB) FROM NUMBERS GROUP BY NUMB HAVING NUMB=99
) A
------用时14秒-------------------------------------------------------

[叶子]

我电脑运行1分钟没有结果。
我i5的处理器不给力呀

[chuanzhang5687]

怎么看查询时间

[jxqn_liu]

我擦，运行一段太不给力

DECLARE @i INT
SET @i = 0
WHILE @i < 1000000
BEGIN
    INSERT INTO NUMBERS VALUES(CAST(RAND()*1000 AS INT))
    SET @i = @i + 1
END
SELECT X99=COUNT(NUM) FROM NUMBERS GROUP BY NUM HAVING NUM=99

花了5分多钟

[-晴天]

;with c1 as(
select top 10000 newid()id from sysobjects a,sysobjects b,sysobjects c
),c2 as(
select convert(varchar(36),id)id from c1
),c3 as(
select ascii(substring(id,1,1))+ascii(substring(id,2,1))*2+ascii(substring(id,3,1))*3+ascii(substring(id,4,1))*4+ascii(substring(id,5,1))*5
+ascii(substring(id,6,1))*6+ascii(substring(id,7,1))*7+ascii(substring(id,8,1))*8+ascii(substring(id,10,1))*10+ascii(substring(id,11,1))*11
+ascii(substring(id,12,1))*12+ascii(substring(id,13,1))*13+ascii(substring(id,15,1))*15+ascii(substring(id,16,1))*16+ascii(substring(id,17,1))*17
+ascii(substring(id,18,1))*18+ascii(substring(id,20,1))*20+ascii(substring(id,21,1))*21+ascii(substring(id,22,1))*22+ascii(substring(id,23,1))*23
+ascii(substring(id,25,1))*25+ascii(substring(id,26,1))*26+ascii(substring(id,27,1))*27+ascii(substring(id,28,1))*28+ascii(substring(id,29,1))*29
+ascii(substring(id,30,1))*30+ascii(substring(id,31,1))*31+ascii(substring(id,32,1))*32+ascii(substring(id,33,1))*33+ascii(substring(id,34,1))*34
+ascii(substring(id,35,1))*35+ascii(substring(id,36,1))*36-1472 id from c2
),c4 as(
select abs(id-(select avg(id) from c3))%1000 id from c3
)select count(*)id from c4 where id=99
union all
select id from c4
go
/*
id
-----------
11               --这一行是99的个数
907
998
49
660
513
41
701
390
767
471
379
17
526
819
......

126
204
176
9
808

(10001 行受影响)


*/

[Billy]

IF EXISTS(SELECT 1 FROM sys.objects WHERE [NAME] = 'NUMBERS')
    DROP TABLE NUMBERS
GO
CREATE TABLE NUMBERS(
    NUM INT
)
GO
DECLARE @i INT
SET @i = 0
WHILE @i < 1000000
BEGIN
    INSERT INTO NUMBERS VALUES(CAST(RAND()*1000 AS INT))
    SET @i = @i + 1
END
SELECT X99=COUNT(NUM) FROM NUMBERS GROUP BY NUM HAVING NUM=99

--09:46
--上个WC + for a smoke
--普通办公机

[竹林听雨2005]

搞了1分钟，才写入20W条记录，悲剧。

[LOSTCHARON]

悲剧 运行了20分钟 才生成30万条记录

[duoxu1983]

;with t as (
select top 100000  CONVERT(int, ABS(CHECKSUM(NEWID())), 8)%1000 id from sysobjects a,sysobjects b,sysobjects c
)
select id,count(*) from t
group by id
having id=99

[水向东流时光怎么偷]

电脑不给力

[guguda2008]

运行时间0秒

IF OBJECT_ID('TEMPDB..#T') IS NOT NULL DROP TABLE #T
GO
CREATE TABLE #T (
COL1 INT
)
INSERT INTO #T
SELECT TOP 1000000 CHECKSUM(NEWID()) FROM MASTER..SPT_VALUES T1,MASTER..SPT_VALUES T2,MASTER..SPT_VALUES T3,MASTER..SPT_VALUES T4
GO
CREATE INDEX INX_#T_COL1 ON #T (COL1)
SELECT * FROM #T WHERE COL1=99

[simbabest]

我的烂机器跑了30分钟还没写完100万行记录;

[lanmengxjh]

晕倒，第一段用了将近6分钟

[罗马和尚]

926

[竹林听雨2005]

上面这方法好像永远得不到数值为99的记录，呵呵。

IF OBJECT_ID('TEMPDB..#T') IS NOT NULL DROP TABLE #T
GO
CREATE TABLE #T (
COL1 INT
)
INSERT INTO #T
SELECT TOP 1000000 abs(checksum(NEWID()))/10000000 FROM MASTER..SPT_VALUES T1,MASTER..SPT_VALUES T2,MASTER..SPT_VALUES T3,MASTER..SPT_VALUES T4
GO
CREATE INDEX INX_#T_COL1 ON #T (COL1)
SELECT * FROM #T WHERE COL1=99
IF OBJECT_ID('TEMPDB..#T') IS NOT NULL DROP TABLE #T

[飞扬的尘埃]

不会吧，我这CPU也就是个Intel 2140而已~
[Quote=引用 1 楼 maco_wang 的回复:]

我电脑运行1分钟没有结果。
我i5的处理器不给力呀
[/Quote]

[飞扬的尘埃]

SQL Server 可以在查询窗口右下角的状态栏看到运行时长。
[Quote=引用 2 楼 chuanzhang5687 的回复:]

怎么看查询时间
[/Quote]

[飞扬的尘埃]

我偷偷地笑`~