代码如下:
select days,
max(decode(flag2, 1, days, null)) over(partition by flag1) new_sunday
from
(select days,
to_char(days, 'iw') flag1,
to_char(days, 'D') flag2,
max(decode(days, to_date(&var_days, 'yyyy/mm/dd'), to_char(days, 'iw'), null)) over() flag3
from t)
where flag1 = flag3
测试如下:
SQL> select * from t;
DAYS
-----------
2016/8/1
2016/8/2
2016/8/3
2016/8/4
2016/8/5
2016/8/6
2016/8/7
2016/10/1
2016/10/2
2016/10/3
2016/10/4
2016/10/5
2016/10/6
2016/10/7
2016/10/8
2016/10/9
2016/10/10
2016/10/11
2016/10/12
2016/10/13
DAYS
-----------
2016/10/14
2016/10/15
2016/10/16
2016/10/17
2016/10/18
2016/10/19
2016/10/20
2016/10/21
2016/10/22
2016/10/23
2016/10/24
2016/10/25
2016/10/26
2016/10/27
2016/10/28
2016/10/29
2016/10/30
2016/10/31
38 rows selected
--test 1
SQL> select days,
2 max(decode(flag2, 1, days, null)) over(partition by flag1) new_sunday
3 from
4 (select days,
5 to_char(days, 'iw') flag1,
6 to_char(days, 'D') flag2,
7 max(decode(days, to_date('2016/8/1', 'yyyy/mm/dd'), to_char(days, 'iw'), null)) over() flag3
8 from t)
9 where flag1 = flag3
10 /
DAYS NEW_SUNDAY
----------- -----------
2016/8/1 2016/8/7
2016/8/2 2016/8/7
2016/8/3 2016/8/7
2016/8/7 2016/8/7
2016/8/5 2016/8/7
2016/8/6 2016/8/7
2016/8/4 2016/8/7
--test 2
SQL> select days,
2 max(decode(flag2, 1, days, null)) over(partition by flag1) new_sunday
3 from
4 (select days,
5 to_char(days, 'iw') flag1,
6 to_char(days, 'D') flag2,
7 max(decode(days, to_date('2016/10/17', 'yyyy/mm/dd'), to_char(days, 'iw'), null)) over() flag3
8 from t)
9 where flag1 = flag3
10 /
DAYS NEW_SUNDAY
----------- -----------
2016/10/17 2016/10/23
2016/10/18 2016/10/23
2016/10/19 2016/10/23
2016/10/23 2016/10/23
2016/10/21 2016/10/23
2016/10/22 2016/10/23
2016/10/20 2016/10/23
7 rows selected