日期:2014-05-17 浏览次数:20685 次
with t as(
select 1 processInstanceId,to_date('2012/01/20','yyyy/mm/dd') createDate from dual
union all
select 2,to_date('2012/01/29','yyyy/mm/dd') from dual
union all
select 3,to_date('2012/01/25','yyyy/mm/dd') from dual
union all
select 4,to_date('2012/02/10','yyyy/mm/dd') from dual
)
select to_char(to_date('2012/01/20', 'yyyy/mm/dd') +
7 *
trunc((createDate - to_date('2012/01/20', 'yyyy/mm/dd')) / 7),
'yyyy/mm/dd')
------其他解决方案--------------------
'--'
------其他解决方案--------------------
to_char(to_date('2012/01/20', 'yyyy/mm/dd') +6+
7 *
trunc((createDate - to_date('2012/01/20', 'yyyy/mm/dd')) / 7),
'yyyy/mm/dd') "date",
count(1) "count"
from t
group by trunc((createDate - to_date('2012/01/20', 'yyyy/mm/dd')) / 7) order by trunc((createDate - to_date('2012/01/20', 'yyyy/mm/dd')) / 7)
date count
---------------------- ----------
2012/01/20--2012/01/26 2
2012/01/27--2012/02/02 1
2012/02/10--2012/02/16 1
with t as(