连续多日 oracle,连续日期合并问题 - Oracle开发 - ITPUB论坛－中国专业的IT技术社区...

你的语句最外层取END_DATE写的太复杂， 完全可以简化， 下次提问的时候就把需求说清楚， 这样就避免大家猜了

测试代码如下

SQL> with test_data as (

2  select '0901248' as user_id, 'T000000221' as team_code,  date'2016-5-6' as insert_date, 0 as valid_flag from dual

3  union all

4  select '0901248' as user_id, 'T000000221' as team_code,  date'2016-5-10' as insert_date, 0 as valid_flag from dual

5  union all

6  select '0901248' as user_id, 'T000000365' as team_code,  date'2016-5-19' as insert_date, 0 as valid_flag from dual

7  union all

8  select '0901248' as user_id, 'T000000377' as team_code,  date'2016-5-23' as insert_date, 0 as valid_flag from dual

9  union all

10  select '0901248' as user_id, 'T000000383' as team_code,  date'2016-5-26' as insert_date, 0 as valid_flag from dual

11  union all

12  select '0901248' as user_id, 'T000000384' as team_code,  date'2016-5-27' as insert_date, 0 as valid_flag from dual

13  union all

14  select '0901248' as user_id, 'T000000221' as team_code,  date'2016-5-30' as insert_date, 0 as valid_flag from dual

15  union all

16  select '0901248' as user_id, 'T000000221' as team_code,  date'2016-5-31' as insert_date, 0 as valid_flag from dual

17  union all

18  select '0901248' as user_id, 'T000000221' as team_code,  date'2016-6-8'  as insert_date, 0 as valid_flag from dual

19  union all

20  select '0901248' as user_id, 'T000000221' as team_code,  date'2016-6-8'  as insert_date, 0 as valid_flag from dual

21  union all

22  select '0901248' as user_id, 'T000000221' as team_code,  date'2016-6-8'  as insert_date, 0 as valid_flag from dual

23  union all

24  select '0901248' as user_id, 'T000000221' as team_code,  date'2016-6-8'  as insert_date, 0 as valid_flag from dual

25  union all

26  select '9917013' as user_id, 'T000000434' as team_code,  date'2017-3-14' as insert_date, 0 as valid_flag from dual

27  union all

28  select '9917013' as user_id, 'T000000434' as team_code,  date'2017-3-14' as insert_date, 0 as valid_flag from dual

29  union all

30  select '9917013' as user_id, 'T000000453' as team_code,  date'2017-3-22' as insert_date, 0 as valid_flag from dual

31  union all

32  select '9917013' as user_id, 'T000000453' as team_code,  date'2017-3-22' as insert_date, 0 as valid_flag from dual

33  union all

34  select '9917013' as user_id, 'T000000434' as team_code,  date'2017-3-28' as insert_date, 0 as valid_flag from dual

35  union all

36  select '9917013' as user_id, 'T000000434' as team_code,  date'2017-3-28' as insert_date, 0 as valid_flag from dual

37  union all

38  select '9917013' as user_id, 'T000000508' as team_code,  date'2018-3-8' as insert_date, 1 as valid_flag from dual

39  )

40  select  user_id,

41          max(team_code) team_code,

42          min(insert_date) start_date,

43          nvl(lead( min(insert_date)) over(partition by user_id order by min(insert_date)) - 1, decode(max(valid_flag), 0, max(insert_date), 1, trunc(sysdate))) end_date,

44          max(valid_flag) valid_flag

45  from

46  (select user_id,

47          team_code,

48          insert_date,

49          valid_flag,

50          max(rn) over(partition by user_id order by insert_date) rn

51  from

52  (select user_id,

53          team_code,

54          insert_date,

55          valid_flag,

56          case

57           when team_code = lag(team_code) over(partition by user_id order by insert_date) then

58                0

59           else

60                row_number() over(partition by user_id order by insert_date)

61          end rn

62     from test_data))

63  group by user_id, rn

64  order by user_id, min(insert_date);

USER_ID TEAM_CODE  START_DATE  END_DATE    VALID_FLAG

------- ---------- ----------- ----------- ----------

0901248 T000000221 2016/5/6    2016/5/18            0

0901248 T000000365 2016/5/19   2016/5/22            0

0901248 T000000377 2016/5/23   2016/5/25            0

0901248 T000000383 2016/5/26   2016/5/26            0

0901248 T000000384 2016/5/27   2016/5/29            0

0901248 T000000221 2016/5/30   2016/6/8             0

9917013 T000000434 2017/3/14   2017/3/21            0

9917013 T000000453 2017/3/22   2017/3/27            0

9917013 T000000434 2017/3/28   2018/3/7             0

9917013 T000000508 2018/3/8    2018/5/14            1

10 rows selected

SQL>