3,492
社区成员
发帖
与我相关
我的任务
分享
select * from tt a,
(select min(datetime) starttime
from tt
start with datetime='200906'
connect by prior datetime=to_char(add_months(to_date(datetime,'yyyymm'),1),'yyyymm')
and prior id=id
and time=0
and prior time=time)
where a.datetime='200906'
select a.id,a.datetime,a.time,min(b.datetime) from tt a left join tt b
on a.id=b.id and b.time=0
and a.datetime>=b.datetime
and not exists(select 1 from tt
where datetime>b.datetime and datetime<=a.datetime and time>0)
where a.datetime='200906'
group by a.id,a.datetime,a.time
with tt as(select '04kcmCSanj' id, '200811' datetime, 23.75 time from dual union all
select '04kcmCSanj' id, '200812' datetime, 0 time from dual union all
select '04kcmCSanj' id, '200901' datetime, 0 time from dual union all
select '04kcmCSanj' id, '200902' datetime, 0 time from dual union all
select '04kcmCSanj' id, '200903' datetime, 30.50 time from dual union all
select '04kcmCSanj' id, '200904' datetime, 29.54 time from dual union all
select '04kcmCSanj' id, '200905' datetime, 0 time from dual union all
select '04kcmCSanj' id, '200906' datetime, 0 time from dual union all
select '04kcmCSanj' id, '200907' datetime, 12.50 time from dual union all
select '04kcmCSanj' id, '200908' datetime, 16.00 time from dual union all
select '04kcmCSanj' id, '200909' datetime, 0 time from dual union all
select '04kcmCSanj' id, '200910' datetime, 16.00 time from dual)
select * from(
select id,datetime,time,
case time when 0 then first_value(datetime)over(partition by id,time,rn2-rn1 order by datetime) end starttime
from(select tt.*,row_number()over(partition by id,time order by datetime)rn1,
row_number()over(partition by id order by datetime)rn2
from tt)
)where datetime='200906'
ID DATETIME TIME STARTTIME
04kcmCSanj 200906 0 200905
with tt as(select '04kcmCSanj' id, '200811' datetime, 23.75 time from dual union all
select '04kcmCSanj' id, '200812' datetime, 0 time from dual union all
select '04kcmCSanj' id, '200901' datetime, 0 time from dual union all
select '04kcmCSanj' id, '200902' datetime, 0 time from dual union all
select '04kcmCSanj' id, '200903' datetime, 30.50 time from dual union all
select '04kcmCSanj' id, '200904' datetime, 29.54 time from dual union all
select '04kcmCSanj' id, '200905' datetime, 0 time from dual union all
select '04kcmCSanj' id, '200906' datetime, 0 time from dual union all
select '04kcmCSanj' id, '200907' datetime, 12.50 time from dual union all
select '04kcmCSanj' id, '200908' datetime, 16.00 time from dual union all
select '04kcmCSanj' id, '200909' datetime, 0 time from dual union all
select '04kcmCSanj' id, '200910' datetime, 16.00 time from dual)
select id,datetime,time,decode(time,0,d)starttime
from(
select t1.*,connect_by_root datetime d from (
select tt.*,row_number()over(partition by id order by datetime)rn,
lag(time)over(partition by id order by datetime)lg from tt)t1
start with time=0 and lg>0 or time>0
connect by prior rn=rn-1 and prior time=time and time=0)
ID DATETIME TIME STARTTIME
04kcmCSanj 200811 23.75
04kcmCSanj 200812 0 200812
04kcmCSanj 200901 0 200812
04kcmCSanj 200902 0 200812
04kcmCSanj 200903 30.5
04kcmCSanj 200904 29.54
04kcmCSanj 200905 0 200905
04kcmCSanj 200906 0 200905
04kcmCSanj 200907 12.5
04kcmCSanj 200908 16
04kcmCSanj 200909 0 200909
04kcmCSanj 200910 16
with tt as(select '04kcmCSanj' id, '200811' datetime, 23.75 time from dual union all
select '04kcmCSanj' id, '200812' datetime, 0 time from dual union all
select '04kcmCSanj' id, '200901' datetime, 0 time from dual union all
select '04kcmCSanj' id, '200902' datetime, 0 time from dual union all
select '04kcmCSanj' id, '200903' datetime, 30.50 time from dual union all
select '04kcmCSanj' id, '200904' datetime, 29.54 time from dual union all
select '04kcmCSanj' id, '200905' datetime, 0 time from dual union all
select '04kcmCSanj' id, '200906' datetime, 0 time from dual union all
select '04kcmCSanj' id, '200907' datetime, 12.50 time from dual union all
select '04kcmCSanj' id, '200908' datetime, 16.00 time from dual union all
select '04kcmCSanj' id, '200909' datetime, 0 time from dual union all
select '04kcmCSanj' id, '200910' datetime, 16.00 time from dual)
select id,datetime,time,
case time when 0 then first_value(datetime)over(partition by id,time,rn2-rn1 order by datetime) end starttime
from(select tt.*,row_number()over(partition by id,time order by datetime)rn1,
row_number()over(partition by id order by datetime)rn2
from tt)
order by 1,2
ID DATETIME TIME STARTTIME
04kcmCSanj 200811 23.75
04kcmCSanj 200812 0 200812
04kcmCSanj 200901 0 200812
04kcmCSanj 200902 0 200812
04kcmCSanj 200903 30.5
04kcmCSanj 200904 29.54
04kcmCSanj 200905 0 200905
04kcmCSanj 200906 0 200905
04kcmCSanj 200907 12.5
04kcmCSanj 200908 16
04kcmCSanj 200909 0 200909
04kcmCSanj 200910 16