表:
id money time
01 10 2005-01-02 13:25:13
02 20 2006-03-02 23:27:17
01 30 2007-03-02 23:27:17
....
要求 查询后的格式为
id 2005 2006 2007
01 10 0 30
02 0 20 0
id money time
01 10 2005-01-02 13:25:13
02 20 2006-03-02 23:27:17
01 30 2007-03-02 23:27:17
....
要求 查询后的格式为
id 2005 2006 2007
01 10 0 30
02 0 20 0
select id,sum(decode(to_char(time,'yyyy'),'2005',money,0))) "2005" ,
sum(decode(to_char(time,'yyyy'),'2006',money,0))) "2006" ,
sum(decode(to_char(time,'yyyy'),'2007',money,0))) "2007"
from table_a
group by id
SUM(DECODE(TO_CHAR(TIME, 'yyyy'), '2006', MONEY, 0))) "2006",
SUM(DECODE(TO_CHAR(TIME, 'yyyy'), '2007', MONEY, 0))) "2007"
FROM TABLE
GROUP BY ID
max(case to_char(time,'yyyy') when '2005' then money else 0 end) "2005",
max(case to_char(time,'yyyy') when '2006' then money else 0 end) "2006",
max(case to_char(time,'yyyy') when '2007' then money else 0 end) "2007"
from 表 group by idsql code 动态方法:declare
lv_sql varchar2(1000);
sql_command varchar2(5000);
cursor cur is select time from 表 group by time;
begin
sql_command := 'select id';
for i in cur loop
-- decode 写法
sql_command := sql_command||' , max(decode(to_char(time,'yyyy'),'''||i.time||''', money,0)) ' ||i.time;
-- case when 写法
sql_command := sql_command||',max(case to_char(to_char(time,'yyyy') when '''||i.time||''' then money else 0 end '|| i.time;
end loop;
sql_command := sql_command||' from 表 group by id';
lv_sql := 'insert into temp_表 ' || sql_command;
dbms_output.put_line(lv_sql);
execute immediate lv_sql;
end;
temp_表的作用:主要用于显示查询结果
希望对你对帮助