年份不固定 支持临时表 case ... when ... then ... else ... end好像没有
下面的是我針對SQL Server寫的, 其中你的table1的三個字段我假設為 name varchar,year varchar,cost int 如果不是這樣的字段類型,你可以在改改. 但是不能保證這些script一定能在mysql上用. 只是提供一種思路而已.create procedure sg as declare @v_create varchar(1000) declare @v_alter varchar(1000) declare @v_year varchar(4) declare @v_insert varchar(1000) declare @v_update varchar(1000) select distinct year into #temp_year from table1; set @v_create = 'create table #temp_1(name varchar(10)' declare mycursor cursor for select year from #temp_year; open mycursor; fetch mycursor into :@v_year; set @v_create = @v_create + ','+@v_year+' int)'; exec (@v_create) set @v_insert = 'insert into #temp_1 select name,sum(isnull(cast,0)) from table1 where year ='''+@v_year +''' group by name '; exec (@v_insert); while @@fetch_status = 0 begin set @v_alter = 'alter table #temp_1 add column '+@v_year; exec (@v_alter); set @v_update = 'update #temp_1 set '+@v_year +' = (select sum(isnull(cast,0)) from table1 a where a.name = #temp_1.name and a.year ='''+@v_year +''' group by name '; exec (@v_update); fetch mycursor into :@v_year; end close mycursor; DEALLOCATE mycursor; select * from #temp_1;
支持临时表
case ... when ... then ... else ... end好像没有
其中你的table1的三個字段我假設為
name varchar,year varchar,cost int
如果不是這樣的字段類型,你可以在改改.
但是不能保證這些script一定能在mysql上用.
只是提供一種思路而已.create procedure sg
as
declare @v_create varchar(1000)
declare @v_alter varchar(1000)
declare @v_year varchar(4)
declare @v_insert varchar(1000)
declare @v_update varchar(1000)
select distinct year into #temp_year from table1;
set @v_create = 'create table #temp_1(name varchar(10)'
declare mycursor cursor for select year from #temp_year;
open mycursor;
fetch mycursor into :@v_year;
set @v_create = @v_create + ','+@v_year+' int)';
exec (@v_create)
set @v_insert = 'insert into #temp_1 select name,sum(isnull(cast,0))
from table1
where year ='''+@v_year
+''' group by name ';
exec (@v_insert);
while @@fetch_status = 0
begin
set @v_alter = 'alter table #temp_1 add column '+@v_year;
exec (@v_alter);
set @v_update = 'update #temp_1 set '+@v_year
+' = (select sum(isnull(cast,0)) from table1 a
where a.name = #temp_1.name and a.year ='''+@v_year
+''' group by name ';
exec (@v_update);
fetch mycursor into :@v_year;
end
close mycursor;
DEALLOCATE mycursor;
select * from #temp_1;
http://www.csdn.net/expert/TopicView.asp?id=227364
http://www.csdn.net/expert/TopicView.asp?id=261695