mysql 中设置为支持中文
vi etc / my.cnf
[ mysqld ]
# add
default - character - set = gb2312
建表
drop table table_name
create table table_name( mon varchar ( 10 ),per varchar ( 10 ),value int )
插入数据
insert into table_name
select ' 一月 ' , ' 张三 ' , 10 UNION all
select ' 二月 ' , ' 李四 ' , 30 UNION all
select ' 三月 ' , ' 王五 ' , 50 UNION all
select ' 四月 ' , ' 陈六 ' , 60 UNION all
select ' 五月 ' , ' 刘七 ' , 78 UNION all
select ' 一月 ' , ' 陈八 ' , 90
存储过程aa
DELIMITER $$
DROP PROCEDURE IF EXISTS `radius`.`aa`$$
CREATE DEFINER = `root`@` % ` PROCEDURE `aa`()
BEGIN
vi etc / my.cnf
[ mysqld ]
# add
default - character - set = gb2312
建表
drop table table_name
create table table_name( mon varchar ( 10 ),per varchar ( 10 ),value int )
插入数据
insert into table_name
select ' 一月 ' , ' 张三 ' , 10 UNION all
select ' 二月 ' , ' 李四 ' , 30 UNION all
select ' 三月 ' , ' 王五 ' , 50 UNION all
select ' 四月 ' , ' 陈六 ' , 60 UNION all
select ' 五月 ' , ' 刘七 ' , 78 UNION all
select ' 一月 ' , ' 陈八 ' , 90
存储过程aa
DELIMITER $$
DROP PROCEDURE IF EXISTS `radius`.`aa`$$
CREATE DEFINER = `root`@` % ` PROCEDURE `aa`()
BEGIN
#tangwf 2007-11-23
declare $stm varchar ( 500 );
declare $rowcnt int ;
declare $mycnt int ;
declare $i int ;
set $mycnt = 0 ;
set $i = 1 ;
set $stm = ' select per ' ;
select count ( distinct mon) into $rowcnt from table_name;
WHILE $i <= $rowcnt DO
set @i : = 0 ;
select col from ( select ( @i : = @i + 1 ) as iden,CONCAT($stm, ' ,SUM(case mon when ''' ,mon, ''' then value else 0 end) as ''' ,mon, '''' ) as col from ( select distinct mon from table_name) as tb) as tb1 where iden = $i into $stm;
Set $i: = $i + 1 ;
END WHILE ;
Set @stm = concat($stm, ' from table_name group by per; ' );
prepare s from @stm ;
execute s;
deallocate prepare s;
END $$
DELIMITER ;
执行存储过程
call aa()
结果
per 一月 二月 三月 四月 五月
李四 0 30 0 0 0
刘七 0 0 0 0 78
王五 0 0 50 0 0
张三 10 0 0 0 0
陈六 0 0 0 60 0
陈八 90 0 0 0 0
declare $stm varchar ( 500 );
declare $rowcnt int ;
declare $mycnt int ;
declare $i int ;
set $mycnt = 0 ;
set $i = 1 ;
set $stm = ' select per ' ;
select count ( distinct mon) into $rowcnt from table_name;
WHILE $i <= $rowcnt DO
set @i : = 0 ;
select col from ( select ( @i : = @i + 1 ) as iden,CONCAT($stm, ' ,SUM(case mon when ''' ,mon, ''' then value else 0 end) as ''' ,mon, '''' ) as col from ( select distinct mon from table_name) as tb) as tb1 where iden = $i into $stm;
Set $i: = $i + 1 ;
END WHILE ;
Set @stm = concat($stm, ' from table_name group by per; ' );
prepare s from @stm ;
execute s;
deallocate prepare s;
END $$
DELIMITER ;
执行存储过程
call aa()
结果
per 一月 二月 三月 四月 五月
李四 0 30 0 0 0
刘七 0 0 0 0 78
王五 0 0 50 0 0
张三 10 0 0 0 0
陈六 0 0 0 60 0
陈八 90 0 0 0 0