-
首先将row_number按照日期排序
-
将日期的日,比如2016-7-1,将1截取出来转换成数字
-
把这个日期数字减去row_number计算出来值,作为分组号,因为不连续的值算出来的分组号就不一致;
-
根据分组号字段进行group by,可以算出每段连续上班的开始、结束时间、天数
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
|
with
t1
as
(
select
date
'2016-7-1'
d1
from
dual
union
select
date
'2016-7-2'
d1
from
dual
union
select
date
'2016-7-4'
d1
from
dual
union
select
date
'2016-7-5'
d1
from
dual
union
select
date
'2016-7-6'
d1
from
dual
union
select
date
'2016-7-7'
d1
from
dual
union
select
date
'2016-7-8'
d1
from
dual
union
select
date
'2016-7-9'
d1
from
dual
union
select
date
'2016-7-11'
d1
from
dual
union
select
date
'2016-7-12'
d1
from
dual
union
select
date
'2016-7-13'
d1
from
dual
union
select
date
'2016-7-14'
d1
from
dual
union
select
date
'2016-7-18'
d1
from
dual
union
select
date
'2016-7-19'
d1
from
dual
)
select
gn,
min
(d1),
max
(d1),
count
(*)
from
(
select
d1,to_number(to_char(d1,
'dd'
))-row_number() over(
order
by
d1) gn
from
t1
)
group
by
gn
order
by
2
|