问个算去年同期值的sql
本帖最后由 charlesxu 于 2013-05-21 11:27:01 编辑 表中数据如下：
日期 得分
2012-1 85
2012-2 98
2012-3 56
2012-4 66
2012-5 34
。。。
。。。
2013-1 82
2013-2 78
2013-3 29
2013-4 77

sql查4个值，日期、得分、去年同期得分、历史最高得分，希望取到的值如下：

日期 得分 去年同期得分 历史最高得分
2012-1 85
2012-2 98
2012-3 56
2012-4 66
2012-5 34
。。。
。。。
2013-1 82 85 85
2013-2 78 98 98
2013-3 29 56 29
2013-4 66 77 77

请问这个sql怎么写？
[解决办法]
这效果么
如果日期格式都是固定的 可以
如果是2011-1那可能要找到'-'的位置开始截取了 稍微麻烦了点

with t1 as
(
     select '2011-01' dt,85 sc from dual union all
     select '2011-02' dt,98 sc from dual union all
     select '2011-03' dt,56 sc from dual union all
     select '2011-04' dt,66 sc from dual union all
     select '2012-01' dt,33 sc from dual union all
     select '2012-02' dt,34 sc from dual union all
     select '2012-03' dt,54 sc from dual union all
     select '2012-04' dt,69 sc from dual union all
     select '2013-01' dt,97 sc from dual union all
     select '2013-02' dt,43 sc from dual union all
     select '2013-03' dt,44 sc from dual union all
     select '2013-04' dt,34 sc from dual 
)

select dt,sc,s_sc,
       (select max(sc) from t1 where t1.dt <= t.dt and substr(t1.dt,-2)=substr(t.dt,-2)) m_sc
from
(
select dt,sc,
       case row_number() over(partition by substr(dt,-2) order by rownum) 
       when 1 then null 
       else lag(sc) over(order by substr(dt,-2),substr(dt,1,4)) end s_sc
from t1
) t
order by dt

      dt      sc    s_sc    m_sc
------------------------------------------
12011-018585 
 
22011-029898
32011-035656
42011-046666
52012-01338585
62012-02349898
72012-03545656
82012-04696669
92013-01973397
102013-02433498
112013-03445456
122013-04346969

with t1 as
 (select '2011-01' dt, 85 sc
    from dual
  union all
  select '2011-02' dt, 98 sc
    from dual
  union all
  select '2011-03' dt, 56 sc
    from dual
  union all
  select '2011-04' dt, 66 sc
    from dual
  union all
  select '2012-01' dt, 33 sc
    from dual
  union all
  select '2012-02' dt, 34 sc
    from dual
  union all
  select '2012-03' dt, 54 sc
    from dual
  union all
  select '2012-04' dt, 69 sc
    from dual
  union all
  select '2013-01' dt, 97 sc
    from dual
  union all
  select '2013-02' dt, 43 sc
    from dual
  union all
  select '2013-03' dt, 44 sc
    from dual
  union all
  select '2013-04' dt, 34 sc from dual)
select t1.dt 日期,
       t1.sc 得分,
       (select sc
          from t1 t
         where t.dt =
               to_char(add_months(to_date(t1.dt, 'yyyy-mm'), -12), 'yyyy-mm')) 去年同期得分,
       (select max(sc)
          from t1 t
         where t.dt < t1.dt
           and substr(t1. dt, 6, 2) = substr(t.dt, 6, 2)) 历史最高得分
  from t1;