17,082
社区成员
发帖
与我相关
我的任务
分享
SQL>
SQL> with tab as
2 (
3 select to_date('2007-02-18','yyyy-MM-dd') t_date,'1.5%' t_bl from dual
4 union all
5 select to_date('2007-03-05','yyyy-MM-dd') t_date, '1.8%' t_bl from dual
6 union all
7 select to_date('2007-03-22','yyyy-MM-dd') t_date,'2.0%' t_bl from dual
8 union all
9 select to_date('2007-04-15','yyyy-MM-dd') t_date,'3.0%' t_bl from dual
10 union all
11 select to_date('2007-05-20','yyyy-MM-dd') t_date,'2.0%' t_bl from dual
12 union all
13 select to_date('2007-06-07','yyyy-MM-dd') t_date,'1.7%' t_bl from dual
14 )
15 select t_date, t_date - p_date+1 intv
16 from (select t_date,
17 lag(t_date, 1, trunc(t_date, 'mm')) over(partition by trunc(t_date, 'mm') order by t_date) p_date,
18 t_bl
19 from tab)
20 /
T_DATE INTV
----------- ----------
2007-2-18 18
2007-3-5 5
2007-3-22 18
2007-4-15 15
2007-5-20 20
2007-6-7 7
6 rows selected
SQL