IT序号网

得到连续序号、日期查询

luoye 2021年06月01日 数据库 264 0
  1. 得到连续序号

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


 


评论关闭
IT序号网

微信公众号号:IT虾米 (左侧二维码扫一扫)欢迎添加!

程序生成随机数与SQL语句生成随机数