一条比较难实现的SQL，高手帮帮忙呀！

Question

本帖最后由 wenqc 于 2009-11-07 00:39:12 编辑

Answer 1

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,2ID 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

Answer 2

再写一个，没有上面的好
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

Answer 3

真强！！原来分析函数还可以这么用法，还有一点小问题。
如统计出来的数据
04kcmCSanj    200904    29.54
04kcmCSanj    200905    0    200905
04kcmCSanj    200906    0    200905
5月和6月的都为0，按你那SQL写时，如果只想查询200906的数据，即是在后面加个条件datetime = '200906'
这样查询出来的结果是
04kcmCSanj    200906    0    200906
我实际想得到的结果
04kcmCSanj    200906    0    200905
这样应该怎样实现呢？麻烦大大再指教一下，因为要实现增量更新，每个月更新一次。

Answer 4

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'把条件加在外边

Answer 5

对啊，把条件加在外边，但得到的结果是：
04kcmCSanj    200906    0    200906
即是开始停产日期是当月的了，但实际上，上个月或前几个月就开始停产了
所以我要得到的结果应该是
04kcmCSanj    200906    0    200905

Answer 6

是在我上面的语句外面再加一层
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

Answer 7

哦，我明白你的意思了，开始没看清楚前面有select * 这层，但这样的话，性能太差了，查一个月的数据时，要先把所有数据取出来后再过滤出当月的数据。

Answer 8

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

Answer 9

如果月份连续的话，可以这样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'

Answer 10

非常感谢wildwave兄不断的帮忙！！这么多种方法还是1楼回复那个方法性能最高，决定采纳那个方法了，最近在做一个项目，遇到不懂的还需要wildwave兄和各位高手以后多多帮忙。。再次谢谢啦~~~