得到连续序号、日期查询

得到连续序号

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 */

posted on 2013-05-23 18:04 代岳强阅读(420) 评论(0) 编辑收藏举报