Oracle数据连续时取连续值的第一个

Question

数据表格式：

id                    year    month
110108196802226415    2020    1    
110108196802226415    2020    2    
110108196802226415    2020    3    
110108196802226415    2020    4    
110108196802226415    2020    5    
110108196802226415    2020    7    
110108196802226415    2020    8    
110108196802226415    2020    9    
110108196802226415    2020    10    
110108196802226415    2020    11

想要在oracle中取得下面数据格式，月份连续时取连续值的第一个

结果集            
id                    year    month beginMonth
110108196802226415    2020    1    1
110108196802226415    2020    2    1
110108196802226415    2020    3    1
110108196802226415    2020    4    1
110108196802226415    2020    5    1
110108196802226415    2020    7    7
110108196802226415    2020    8    7
110108196802226415    2020    9    7
110108196802226415    2020    10    7
110108196802226415    2020    11    7

Answer 1

这题有意思,我本来打算用开窗函数来做,但是想想用迭代思路更清晰,就凑了个迭代的sql出来,数据结果是对的,但应该还可以优化

with cte(year,
month,
l,
begin_month,
b) as
 (select year, month, month + 1 l, month begin_month, month b
    from month_test
   where year = 2020
     and month = 1
  union all
  select cte.year,
         cte.l,
         cte.l + 1,
         case
           when b.month is null then
            cte.l + 1
           else
            cte.begin_month
         end,
         b.month
    from cte, month_test b
   where cte.l = b.month(+)
     and cte.l <= 12)
select YEAR, MONTH, begin_month from cte where b is not null