【SQL Server】生成连续的时间或数字
在Oracle里生成连续的时间和数字非常的方便,拿数字举例(日期的话,大差不差)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
SQL> select rownum from dual connect by rownum<=12; ROWNUM ---------- 1 2 3 4 5 6 7 8 9 10 11 12 12 rows selected SQL> |
那么SQL Server如何实现呢?连续日期,如下:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
1> with t as ( select convert(datetime,'11/1/2015',120) dday union all select dateadd(month,1,t.dday) from t where dday < convert(datetime,'5/1/2016',120) ) select * from t 2> go dday ------------------------- 2015-11-01 00:00:00.000 2015-12-01 00:00:00.000 2016-01-01 00:00:00.000 2016-02-01 00:00:00.000 2016-03-01 00:00:00.000 2016-04-01 00:00:00.000 2016-05-01 00:00:00.000 (7 rows affected) 1> |
连续数字
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
1> with t as ( select 1 m union all select m+1 from t where m <12 ) select * from t 2> go m ---- 1 2 3 4 5 6 7 8 9 10 11 12 (12 rows affected) 1> |