在oracle中求一sql语句
已知一表 a 有如下数据
usedate chinaoil exciusoil excichoil wtoil
2009-08-22 1 2 3 4
2009-08-23 9 10 11 12
2009-08-25 13 14 15 16
现在要求得到如下数据
2009-08-22 2009-08-23 2009-08-25
chinaoil 1 9 13
exciusoil 2 10 14
excichoil 3 11 15
wtoil 4 12 16
已知一表 a 有如下数据
usedate chinaoil exciusoil excichoil wtoil
2009-08-22 1 2 3 4
2009-08-23 9 10 11 12
2009-08-25 13 14 15 16
现在要求得到如下数据
2009-08-22 2009-08-23 2009-08-25
chinaoil 1 9 13
exciusoil 2 10 14
excichoil 3 11 15
wtoil 4 12 16
max(case when usedate='2009-08-22' then dd end),
max(case when usedate='2009-08-23' then dd end),
max(case when usedate='2009-08-25' then dd end)
from (
select a.usedate,b.cc,
case when b.cc='chinaoil' then chinaoil
when b.cc='exciusoil' then exciusoil
when b.cc='excichoil' then excichoil
when b.cc='wtoil' then wtoil
end dd
from a,(select 'chinaoil' cc from dual
union all
select 'exciusoil' cc from dual
union all
select 'excichoil' cc from dual
union all
select 'wtoil' cc from dual ) b )
group by cc
;
select max(decode(usedate,'2009-08-22',chinaoil))"2009-08-22",
max(decode(usedate,'2009-08-23',chinaoil))"2009-08-23",
max(decode(usedate,'2009-08-25',chinaoil))"2009-08-25" from a union all
select max(decode(usedate,'2009-08-22',exciusoil)),
max(decode(usedate,'2009-08-23',exciusoil)),
max(decode(usedate,'2009-08-25',exciusoil)) from a union all
select max(decode(usedate,'2009-08-22',excichoil)),
max(decode(usedate,'2009-08-23',excichoil)),
max(decode(usedate,'2009-08-25',excichoil)) from a union all
select max(decode(usedate,'2009-08-22',wtoil)),
max(decode(usedate,'2009-08-23',wtoil)),
max(decode(usedate,'2009-08-25',wtoil)) from a
不过记录条数多的话效率太低
如果只是为了显示效果,建议通过外部工具来实现
title,
sum(decode(udpate, '2009-08-22', cnt, 0)) "2009-08-22",
sum(decode(udpate, '2009-08-23', cnt, 0)) "2009-08-23",
sum(decode(udpate, '2009-08-25', cnt, 0)) "2009-08-25"
from
(select update, 'chinaoil title, chinaoil cnt from t1
union
select update, 'exciusoil' title, exciusoil cnt from t1
union
select update, 'excichoil' title, excichoil cnt from t1
union
select update, 'wtoil' title, wtoil cnt from t1) group by title
可以写个过程,拼出你需要的语句,再执行
sqlstr varchar2(2000):='select title';
begin
for cur1 in (select * from a)
loop
sqlstr:=sqlstr||'
,max(decode(usedate,'''||cur1.usedate||''',cnt))"'||cur1.usedate||'"';
end loop;
sqlstr:=sqlstr||'
from (select usedate,''chinaoil'' title,chinaoil cnt from a union all
select usedate,''exciusoil'',exciusoil from a union all
select usedate,''excichoil'',excichoil from a union all
select usedate,''wtoil'',wtoil from a)
group by title';
dbms_output.put_line(sqlstr);
end;
declare
strSql varchar2(2000);
cursor datelist is select usedate from a;
strDate varchar2(20);
begin
strSql:='select num';
open datelist;
loop
fetch datelist into strDate;
exit when datelist%notfound;
strSql:=strSql||',sum(decode(usedate, '''||strDate||''', cnt,0)) "'||strDate||'"';
end loop;
close datelist;
strSql:=strSql||' from (
select usedate,1 num,chinaoil cnt from a
union select usedate,2 num,exciusoil cnt from a
union select usedate,3 num,excichoil cnt from a
union select usedate,4 num,wtoil cnt from a)
group by num order by num asc';
dbms_output.put_line(strSql);
end;加入一个num用来排序,还可以防止表里没有记录时组合的sql出错