有一表存储内容为:
t_date t_bl
2007-02-18 1.5%
2007-03-05 1.8%
2007-03-22 2.0%
2007-04-15 3.0%
2007-05-20 2.0%
2007-06-07 1.7%
现在我要按月取出2007年3月1日到2007年3月31日总共分了几段,每段有几天?结果如下:日期 天数
2007-03-05 5
2007-03-22 17
2007-03-31 9如何写SQL!!!
t_date t_bl
2007-02-18 1.5%
2007-03-05 1.8%
2007-03-22 2.0%
2007-04-15 3.0%
2007-05-20 2.0%
2007-06-07 1.7%
现在我要按月取出2007年3月1日到2007年3月31日总共分了几段,每段有几天?结果如下:日期 天数
2007-03-05 5
2007-03-22 17
2007-03-31 9如何写SQL!!!
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 76 rows selectedSQL
select to_date('2007-02-18','yyyy-mm-dd') date1,'1.5%' t_b1 from dual
union all
select to_date('2007-03-05','yyyy-mm-dd') date1,'1.5%' t_b1 from dual
union all
select to_date('2007-03-22','yyyy-mm-dd') date1,'1.5%' t_b1 from dual
union all
select to_date('2007-04-15','yyyy-mm-dd') date1,'1.5%' t_b1 from dual
union all
select to_date('2007-05-20','yyyy-mm-dd') date1,'1.5%' t_b1 from dual
union all
select to_date('2007-06-07','yyyy-mm-dd') date1,'1.5%' t_b1 from dual
)
select date1, case when date2 is null then to_number(substr(to_char(date1,'yyyy-mm-dd'),9,2)) else date1 - date2 end days from(
select date1,lag(date1) over(partition by to_char(date1,'yyyy-mm') order by date1) date2 from temp);
select to_date('2007-02-18','yyyy-mm-dd') date1,'1.5%' t_b1 from dual
union all
select to_date('2007-03-05','yyyy-mm-dd') date1,'1.5%' t_b1 from dual
union all
select to_date('2007-03-22','yyyy-mm-dd') date1,'1.5%' t_b1 from dual
union all
select to_date('2007-04-15','yyyy-mm-dd') date1,'1.5%' t_b1 from dual
union all
select to_date('2007-05-20','yyyy-mm-dd') date1,'1.5%' t_b1 from dual
union all
select to_date('2007-06-07','yyyy-mm-dd') date1,'1.5%' t_b1 from dual
)
select date1, case when date2 is null then to_number(substr(to_char(date1,'yyyy-mm-dd'),9,2)) else date1 - date2 end days from(
select date1,lag(date1) over(partition by to_char(date1,'yyyy-mm') order by date1) date2 from temp);结果:
1 2007-2-18 18
2 2007-3-5 5
3 2007-3-22 17
4 2007-4-15 15
5 2007-5-20 20
6 2007-6-7 7