27,579
社区成员
发帖
与我相关
我的任务
分享
创建表的语句如下:
CREATE TABLE dbo.Nums(n INT NOT NULL PRIMARY KEY);
DECLARE @i INT
set @i=1
WHILE @i<8000
BEGIN
INSERT INTO Nums SELECT @i
SET @i=@i+1
END
DECLARE @max AS INT, @rc AS INT;
SET @max = 8000;
SET @rc = 1;
INSERT INTO Nums VALUES(1);
WHILE @rc * 2 <= @max
BEGIN
INSERT INTO dbo.Nums SELECT n + @rc FROM dbo.Nums;
SET @rc = @rc * 2;
END
INSERT INTO dbo.Nums
SELECT n + @rc FROM dbo.Nums WHERE n + @rc <= @max;
GO
;with t1 as
(
select 1 as col
union all
select 1
)
,t2 as
(
select i.col
from t1 as i
cross join t1 as p
cross join t1 as q
)
,t3 as
(
select i.col
from t2 as i
cross join t2 as p
)
,t4 as
(
select i.col
from t3 as i
cross join t3 as p
cross join t3 as q
)
select top (8000) row_number() over(order by t4.col)
from t4
select 1
union
select 2
union
.
.
.
--以下省略8000字
gogodiy 大婶方法执行时间:
select top 8000 ID=row_number() over (order by getdate())
from master..spt_values a,master..spt_values b
--186ms
-------------------
单凭时间来说:第3中方法要快于magician547大婶的方法
declare @d datetime
set @d=getdate()
;
with
L0 as (select 1 as ID union all select 1),
L1 as (select a.ID from L0 as a , L0 as b),
L2 as (select a.ID from L1 as a , L1 as b),
L3 as (select a.ID from L2 as a , L2 as b),
L4 as (select a.ID from L3 as a , L3 as b),
L5 as (select row_number()over(order by ID) as ID from L4)
SELECT * FROM L5 WHERE ID < 8001;
select [语句执行花费时间(毫秒)]=datediff(ms,@d,getdate())
--156ms
---------------------------------------------------------
--@周老师方法:93ms
---------------------------------------------------------
--@左老师方法:720ms
select top 100000 ID=row_number() over (order by getdate())
from master..spt_values a,master..spt_values b