oracle :
SELECT trunc(to_date('2013/10/01', 'yyyy/mm/dd'), 'month') + LEVEL - 1 dates
FROM dual
CONNECT BY rownum < to_date('2013/10/01', 'yyyy/mm/dd') -
trunc(to_date('2013/11/01', 'yyyy/mm/dd'), 'month') + 1
结果:
--------------------
1 2013/10/1
2 2013/10/2
3 2013/10/3
4 2013/10/4
5 2013/10/5
6 2013/10/6
7 2013/10/7
8 2013/10/8
9 2013/10/9
10 2013/10/10
11 2013/10/11
12 2013/10/12
13 2013/10/13
14 2013/10/14
15 2013/10/15
16 2013/10/16
17 2013/10/17
18 2013/10/18
19 2013/10/19
20 2013/10/20
21 2013/10/21
22 2013/10/22
23 2013/10/23
24 2013/10/24
25 2013/10/25
26 2013/10/26
27 2013/10/27
28 2013/10/28
29 2013/10/29
30 2013/10/30
31 2013/10/31
求帮我转换为SQL
------解决思路----------------------
我觉得oracle的这种递归非常蛋疼，要不然为毛11G开始支持CTE的递归呢？
with cte_temp
as
(
select cast('2013-10-1' as date) as dte
union all
select DATEADD(D,1,dte) from cte_temp where dte<dateadd(D,-1,dateadd(MONTH,1,cast('2013-10-1' as date)))
)
select ROW_NUMBER()over(order by dte) as id,dte from cte_temp
id dte
-------------------- ----------
1 2013-10-01
2 2013-10-02
3 2013-10-03
4 2013-10-04
5 2013-10-05
6 2013-10-06
7 2013-10-07
8 2013-10-08
9 2013-10-09
10 2013-10-10
11 2013-10-11
12 2013-10-12
13 2013-10-13
14 2013-10-14
15 2013-10-15
16 2013-10-16
17 2013-10-17
18 2013-10-18
19 2013-10-19
20 2013-10-20
21 2013-10-21
22 2013-10-22
23 2013-10-23
24 2013-10-24
25 2013-10-25
26 2013-10-26
27 2013-10-27
28 2013-10-28
29 2013-10-29
30 2013-10-30
31 2013-10-31
(31 行受影响)