corp_id corp_name value date
1 1-1 2.1 2008-11-11
1 1-1 2.3 2008-11-12
1 1-1 2.5 2008-11-13
... ... ... ..........我现在希望将格式转化成 corp_id corp_name 2008-11-11 2008-11-12 2008-11-13 ......
1 1-1 2.1 2.3 2.5 .......注意:我的数据是要求 距当天前七天的数据,请大家看清楚再回答。。谢谢
希望高手们能赐教。给个解决的思路和方案。。谢谢
1 1-1 2.1 2008-11-11
1 1-1 2.3 2008-11-12
1 1-1 2.5 2008-11-13
... ... ... ..........我现在希望将格式转化成 corp_id corp_name 2008-11-11 2008-11-12 2008-11-13 ......
1 1-1 2.1 2.3 2.5 .......注意:我的数据是要求 距当天前七天的数据,请大家看清楚再回答。。谢谢
希望高手们能赐教。给个解决的思路和方案。。谢谢
select corp_id,
corp_name,
max(decode(dt,to_char(sysdate - 1, 'yyyy-mm-dd'), value)) ,
max(decode(dt, to_char(sysdate - 2, 'yyyy-mm-dd'), value)),
max(decode(dt, to_char(sysdate - 3 , 'yyyy-mm-dd'), value))
from (select *
from t
where t.dt in (select to_char(sysdate - rownum + 1, 'yyyy-mm-dd') dd
from dual
connect by rownum <= 7) )
group by corp_id, corp_name;
首先用这个条件t.dt in (select to_char(sysdate - rownum + 1, 'yyyy-mm-dd') dd
from dual
connect by rownum <= 7)
把七天之内的数据过滤出来,然后再用max(decode),至于max(decode)已给你例子。
SQL> select t.id,t.name,max(t1.value),max(t2.value),max(t3.value) from t
2 left join (select id,value,d from t where d<sysdate-1 and d>sysdate-2) t1 on t.id=t1.id and t.d=t1.d
3 left join (select id,value,d from t where d<sysdate-2 and d>sysdate-3) t2 on t.id=t2.id and t.d=t2.d
4 left join (select id,value,d from t where d<sysdate-3 and d>sysdate-4) t3 on t.id=t3.id and t.d=t3.d
5 group by t.id,t.name; ID NAME MAX(T1.VALUE) MAX(T2.VALUE) MAX(T3.VALUE)
---------- ---- ------------- ------------- -------------
1 1-1 01/02/12 2.2 2.1