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
希望高手们能赐教。给个解决的思路和方案。。谢谢
insert into t values( 1 , '1-1', 2.1 , '2008-11-11');
insert into t values( 1 , '1-1', 2.3 , '2008-11-12');
insert into t values( 1 , '1-1', 2.5 , '2008-11-13');
SQL> select corp_id,
2 corp_name,
3 max(decode(dt, '2008-11-11', value)) "2008-11-11",
4 max(decode(dt, '2008-11-12', value)) "2008-11-12",
5 max(decode(dt, '2008-11-13', value)) "2008-11-13"
6 from t
7 group by corp_id, corp_name;
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
2 2-1 3.3 2008-11-11
2 2-1 2.3 2008-11-12
2 2-1 2.4 2008-11-13
... ... ... .........我现在希望将格式转化成 corp_id corp_name 2008-11-11 2008-11-12 2008-11-13 .... 2008-11-17
1 1-1 2.1 2.3 2.5 .... .....
注意:我的数据是要求 距当天前七天的数据
这个dt列应该是随着数据库的数据变化的
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)已给你例子。
还废品呢。就跟你是oracle大师似的....
SQL> select corp_id,
2 corp_name,
3 (select val from table where data = '2008-11-11') as 2008-11-11,
4 (select val from table where data = '2008-11-12') as 2008-11-12,
5 (select val from table where data = '2008-11-13') as 2008-11-13
7 group by corp_id, corp_name;
SQL> select * from t; ID NAME VALUE D
---------- ---- ---------- --------------
1 1-1 2.1 27-12月-08
1 1-1 2.2 28-12月-08
1 1-1 2.3 29-12月-08
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 where t.id=1 group by t.id,t.name
6 / ID NAME MAX(T1.VALUE) MAX(T2.VALUE) MAX(T3.VALUE)
---------- ---- ------------- ------------- -------------
1 1-1 2.3 2.2 2.1