下边是我写的存储过程:
create procedure test1()begin
DECLARE done int default 0;
DECLARE asubject CHAR(20) character set gbk;
DECLARE str VARCHAR(1000) default '';
DECLARE cur1 CURSOR FOR select DISTINCT subject from test ;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;
open cur1;
REPEAT
if not done then
FETCH cur1 INTO asubject;
set str=CONCAT(str, ',', 'SUM(if(subject=''', asubject, ''', sources, 0))', '''', asubject,'''');
end IF;
UNTIL done END REPEAT;
set @sqlString=CONCAT(' select number as 学号',str, ' from test group by number ');
prepare sqlstmt from @sqlString;
execute sqlstmt;
deallocate prepare sqlstmt;end
结果:学号 语文 数学 英语 英语1
1 60 70 75 75
2 55 53 75 75
3 80 0 0 0
4 0 0 0 0
5 0 0 91 91
表里面是没有英语1这一科目的,但是查询出来的却多而来这一列
create procedure test1()begin
DECLARE done int default 0;
DECLARE asubject CHAR(20) character set gbk;
DECLARE str VARCHAR(1000) default '';
DECLARE cur1 CURSOR FOR select DISTINCT subject from test ;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;
open cur1;
REPEAT
if not done then
FETCH cur1 INTO asubject;
set str=CONCAT(str, ',', 'SUM(if(subject=''', asubject, ''', sources, 0))', '''', asubject,'''');
end IF;
UNTIL done END REPEAT;
set @sqlString=CONCAT(' select number as 学号',str, ' from test group by number ');
prepare sqlstmt from @sqlString;
execute sqlstmt;
deallocate prepare sqlstmt;end
结果:学号 语文 数学 英语 英语1
1 60 70 75 75
2 55 53 75 75
3 80 0 0 0
4 0 0 0 0
5 0 0 91 91
表里面是没有英语1这一科目的,但是查询出来的却多而来这一列
这句下面
select @sqlString;
@sqlString
(Null)
DECLARE done int default 0;
DECLARE asubject CHAR(20) character set gbk;
DECLARE str VARCHAR(1000) default '';
DECLARE cur1 CURSOR FOR select DISTINCT subject from test ;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;
open cur1;
REPEAT
FETCH cur1 INTO asubject;
if not done then
set str=CONCAT(str, ',', 'SUM(if(subject=''', asubject, ''', sources, 0))', '''', asubject,'''');
end IF;
UNTIL done END REPEAT;
set @sqlString=CONCAT(' select number as 学号',str, ' from test group by number ');
prepare sqlstmt from @sqlString;
execute sqlstmt;
deallocate prepare sqlstmt;end
在if not done then
这里有什么错么大虾,不是循环取出列值,直到取完吗?