oracle查询11100011获取000左边第一个1

Question

原始数据

with a(name,num,a_date) as (            
    select '李','7', to_date('2021-12-20','yyyy-MM-dd') from dual union all
    select '李','7', to_date('2021-12-07','yyyy-MM-dd') from dual union all
    select '李','7', to_date('2021-09-15','yyyy-MM-dd') from dual union all
    select '李','2', to_date('2021-09-02','yyyy-MM-dd') from dual union all
    select '李','8', to_date('2021-07-26','yyyy-MM-dd') from dual union all
    select '李','4', to_date('2021-06-20','yyyy-MM-dd') from dual union all
    select '李','4', to_date('2021-06-10','yyyy-MM-dd') from dual union all
    select '李','9', to_date('2021-05-10','yyyy-MM-dd') from dual union all
    select '李','1', to_date('2021-05-05','yyyy-MM-dd') from dual union all
    select '张','3', to_date('2022-12-01','yyyy-MM-dd') from dual union all
    select '张','5', to_date('2022-11-01','yyyy-MM-dd') from dual union all
    select '张','10',to_date('2022-10-01','yyyy-MM-dd') from dual union all
    select '张','1', to_date('2022-09-01','yyyy-MM-dd') from dual 
)
select * from a order by a_date desc;

num: 8，9,10是负的，1-7是正的。需要以8或者9或者10分割，取出1-7的每一条数据，增加一列记录第一次出现的时间a_date_last

想要的结果

with result(name,num,a_date,a_date_last) as(
    select '李','7', to_date('2021-12-20','yyyy-MM-dd'),to_date('2021-09-02','yyyy-MM-dd') from dual union all
    select '李','7', to_date('2021-12-07','yyyy-MM-dd'),to_date('2021-09-02','yyyy-MM-dd') from dual union all
    select '李','7', to_date('2021-09-15','yyyy-MM-dd'),to_date('2021-09-02','yyyy-MM-dd') from dual union all
    select '李','2', to_date('2021-09-02','yyyy-MM-dd'),to_date('2021-09-02','yyyy-MM-dd') from dual union all
    select '李','4', to_date('2021-06-20','yyyy-MM-dd'),to_date('2021-06-10','yyyy-MM-dd') from dual union all
    select '李','4', to_date('2021-06-10','yyyy-MM-dd'),to_date('2021-06-10','yyyy-MM-dd') from dual union all
    select '李','1', to_date('2021-05-05','yyyy-MM-dd'),to_date('2021-05-05','yyyy-MM-dd') from dual union all
    
    select '张','3', to_date('2022-12-01','yyyy-MM-dd'),to_date('2022-11-01','yyyy-MM-dd') from dual union all
    select '张','5', to_date('2022-11-01','yyyy-MM-dd'),to_date('2022-11-01','yyyy-MM-dd') from dual union all
    select '张','1', to_date('2022-09-01','yyyy-MM-dd'),to_date('2022-09-01','yyyy-MM-dd') from dual 
)
select * from result order by a_date desc;

Answer 1

with a(name,num,a_date) as (            
    select '李','7', to_date('2021-12-20','yyyy-MM-dd') from dual union all
    select '李','7', to_date('2021-12-07','yyyy-MM-dd') from dual union all
    select '李','7', to_date('2021-09-15','yyyy-MM-dd') from dual union all
    select '李','2', to_date('2021-09-02','yyyy-MM-dd') from dual union all
    select '李','8', to_date('2021-07-26','yyyy-MM-dd') from dual union all
    select '李','4', to_date('2021-06-20','yyyy-MM-dd') from dual union all
    select '李','4', to_date('2021-06-10','yyyy-MM-dd') from dual union all
    select '李','9', to_date('2021-05-10','yyyy-MM-dd') from dual union all
    select '李','1', to_date('2021-05-05','yyyy-MM-dd') from dual union all
    select '张','3', to_date('2022-12-01','yyyy-MM-dd') from dual union all
    select '张','5', to_date('2022-11-01','yyyy-MM-dd') from dual union all
    select '张','10',to_date('2022-10-01','yyyy-MM-dd') from dual union all
    select '张','1', to_date('2022-09-01','yyyy-MM-dd') from dual 
)
select name,num ,a_date,sd,min(a_date) over(partition by name,sd) a_date_last from (
select t.*,sum(d) over (partition by name order by a_date) sd
 from (
select a.*,
       case
         when num in (8,9,10) then
          1
         else
          0
       end d
  from a) t
  ) where num not in (8,9,10) 
  group by name,num ,a_date,sd order by a_date desc;
  ;