sql - 获取包含系统日期和状态的行

Question

>          cycleNO| month |   year|  start date |    close date         |state | prv month  | next mnt 
>             1      4        2012    1/4/2012     30/4/2012 23:59:59      1       3                5
>             1      5        2012    1/5/2012     31/5/2012 23:59:59      0       4                6  
>             1      6        2012    1/6/2012     30/6/2012 23:59:59      0       5                7  
>             1      7        2012    1/7/2012     31/7/2012 23:59:59      0       6                8
>             2      4        2012    1/4/2012     30/4/2012 23:59:59      1       3                5
>             2      5        2012    1/5/2012     31/5/2012 23:59:59      0       4                6  
>             2      6        2012    1/6/2012     30/6/2012 23:59:59      0       5                7  
>             2      7        2012    1/7/2012     31/7/2012 23:59:59      0       6                8

我有一个像上面那样的表 (cycle_set) 并且想要获取 (cycleNO,month,year,start date,close date) 的条件是 state =0 和 start-close date 其中包含系统日期也在即时下个月状态 =0。

输出应该是:

cycleNO | month | year | start date | close date 
1      5        2012    1/5/2012     31/5/2012 23:59:59
1      6        2012    1/6/2012     30/6/2012 23:59:59
2      5        2012    1/5/2012     31/5/2012 23:59:59
2      6        2012    1/6/2012     30/6/2012 23:59:59

Answer 1

select cycleNO,month,year,start_date,close_date 
FROM cycle_set
WHERE state=0 and sysdate between start_date and close_date

更新:如果您想同时获得当前和下个月:

select cycleNO, month, year, start_date, close_date 
FROM cycle_set
WHERE state=0 
   and ( sysdate between start_date and close_date or --current month
         sysdate between add_months(start_date,-1)  and close_date --next_month
        )