- 得到连续序号
SQL2000：
1：select number from master..spt_values where type='p' --0-255
2： select top 10000 id=identity(int,1,1) into #t from sysobjects,syscolumns
SQL2005：
1：CTE方式递归
with cte as ( select 1 as num union all select num +1 from cte where num<1000 ) select * from cte option(maxrecursion 0)
2:通过系统表生成行号
select top 1000 num=ROW_NUMBER() over (order by getdate()) from syscolumns a,sys.columns b
3：生成一个数字表，效率非常高
1 create function dbo.fn_nums(@n as bigint) 2 returns table 3 as 4 return 5 with 6 t1 as (select 1 as c union all select 1), 7 t2 as (select 1 as c from t1 as a,t1 as b), 8 t3 as (select 1 as c from t2 as a,t2 as b), 9 t4 as (select 1 as c from t3 as a,t3 as b), 10 t5 as (select 1 as c from t4 as a,t4 as b), 11 t6 as (select 1 as c from t5 as a,t5 as b), 12 t7 as (select row_number() over(order by c) as n from t6) 13 select n from t7 where n<@n; 14 go 15 --测试 16 17 select * from dbo.fn_nums(1000)
2.得到连续日期（需要借助一个有连续序号的表）
--〉生成连续日期的方法
根据开始时间，结束时间 生成连续的时间
1 IF OBJECT_ID('tempdb..#t') IS NOT NULL DROP TABLE #t 2 GO 3 create table #t(id int identity,Dt varchar(10)) 4 go 5 declare @starttime datetime,@endtime datetime 6 set @starttime = '2013-05-20' 7 set @endtime ='2013-05-23' 8 9 10 insert #t 11 select convert(varchar(10),dateadd(day,number,@starttime),120) dt 12 from master..spt_values 13 where type='P' and number between 0 and datediff(day,@starttime,@endtime) 14 --结果 15 select * from #t 16 17 /* 18 id Dt 19 ----------- ---------- 20 1 2010-05-01 21 2 2010-05-02 22 3 2010-05-03 23 4 2010-05-04 24 5 2010-05-05 25 6 2010-05-06 26 7 2010-05-07 27 8 2010-05-08 28 9 2010-05-09 29 10 2010-05-10 30 11 2010-05-11 31 12 2010-05-12 32 13 2010-05-13 33 14 2010-05-14 34 15 2010-05-15 35 16 2010-05-16 36 17 2010-05-17 37 18 2010-05-18 38 19 2010-05-19 39 20 2010-05-20 40 21 2010-05-21 41 22 2010-05-22 42 23 2010-05-23 43 24 2010-05-24 44 25 2010-05-25 45 26 2010-05-26 46 27 2010-05-27 47 28 2010-05-28 48 29 2010-05-29 49 30 2010-05-30 50 31 2010-05-31 51 52 (31 行受影响) 53 */
