CREATE PROCEDURE proc_sms2
(
)LABEL_PROC:
BEGIN
DECLARE v_sql varchar(200);
SET @v_sql = CONCAT('select Name as ','姓名');
select @v_sql = CONCAT(@sql , ' , sum(case Subject when ''' , Subject , '''
then Result end) [' , Subject , ']')
from (select distinct Subject from sms2) as a;
SET @v_sql = CONCAT(@v_sql , ' from sms2 group by name');
EXECUTE v_sql;
END LABEL_PROC
就是不执行请问哪有错误应该是语法的错误,本人对mysql存储过程不熟悉
(
)LABEL_PROC:
BEGIN
DECLARE v_sql varchar(200);
SET @v_sql = CONCAT('select Name as ','姓名');
select @v_sql = CONCAT(@sql , ' , sum(case Subject when ''' , Subject , '''
then Result end) [' , Subject , ']')
from (select distinct Subject from sms2) as a;
SET @v_sql = CONCAT(@v_sql , ' from sms2 group by name');
EXECUTE v_sql;
END LABEL_PROC
就是不执行请问哪有错误应该是语法的错误,本人对mysql存储过程不熟悉
DELIMITER $$
drop procedure if exists proc_sms2$$
CREATE
/*[DEFINER = { user | CURRENT_USER }]*/
PROCEDURE `rr`.`proc_sms2`()
/*LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'*/
BEGIN
DECLARE v_sql varchar(200);set v_sql=concat('select name as 姓名','');
select @v_sql = CONCAT(@sql , ' , sum(case Subject when ''' , Subject , '''
then Result end) [' , Subject , ']')
from (select distinct Subject from sms2) as a;
SET @v_sql = CONCAT(@v_sql , ' from sms2 group by name');
PREPARE stmt2 FROM @v_sql;
EXECUTE stmt2;
END$$DELIMITER ;
Query OK, 0 rows affected (0.00 sec)mysql>
mysql> CREATE TABLE student
-> (
-> id int auto_increment not null primary key,
-> `name` nvarchar(20),
-> `subject` nvarchar(20),
-> score int
-> ) ENGINE=MYISAM;
Query OK, 0 rows affected (0.06 sec)mysql>
mysql> INSERT student(`name`,`subject`,score) VALUES('小梁','语文',90);
Query OK, 1 row affected (0.03 sec)mysql> INSERT student(`name`,`subject`,score) VALUES('小梁','数学',80);
Query OK, 1 row affected (0.00 sec)mysql> INSERT student(`name`,`subject`,score) VALUES('小梁','英语',70);
Query OK, 1 row affected (0.00 sec)mysql>
mysql> INSERT student(`name`,`subject`,score) VALUES('依依','语文',80);
Query OK, 1 row affected (0.00 sec)mysql> INSERT student(`name`,`subject`,score) VALUES('依依','数学',90);
Query OK, 1 row affected (0.00 sec)mysql> INSERT student(`name`,`subject`,score) VALUES('依依','英语',99);
Query OK, 1 row affected (0.00 sec)mysql> DELIMITER $$
mysql>
mysql> DROP PROCEDURE IF EXISTS p_RowColumnCross $$
Query OK, 0 rows affected (0.00 sec)mysql>
mysql> CREATE PROCEDURE p_RowColumnCross()
-> BEGIN
-> DECLARE s nvarchar(8000);
-> DECLARE `v_subject` nvarchar(64);
->
-> DECLARE cur cursor for select distinct `subject` from student;
-> DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET v_subject=null;
->
-> open cur;
-> fetch cur into v_subject;
-> set @s='';
-> while v_subject is not null do
-> set @s=concat(@s,',max(case when subject=''',v_subject,''' then score else 0 end) as ',v_subject);
->
-> fetch cur into v_subject;
-> end while;
-> close cur;
->
-> set @s=concat('select name as 姓名',@s,' from student group by name');
->
-> prepare stmt from @s;
-> execute stmt;
-> deallocate prepare stmt;
->
-> END$$
Query OK, 0 rows affected (0.00 sec)mysql>
mysql> DELIMITER ;
mysql> CALL p_RowColumnCross();
+------+------+------+------+
| 姓名 | 语文 | 数学 | 英语 |
+------+------+------+------+
| 依依 | 80 | 90 | 99 |
| 小梁 | 90 | 80 | 70 |
+------+------+------+------+
2 rows in set (0.01 sec)
(
id int auto_increment not null primary key,
`name` nvarchar(20),
`subject` nvarchar(20),
score int
) ;INSERT student(`name`,`subject`,score) VALUES('小梁','语文',90);
INSERT student(`name`,`subject`,score) VALUES('小梁','数学',80);
INSERT student(`name`,`subject`,score) VALUES('小梁','英语',70);INSERT student(`name`,`subject`,score) VALUES('依依','语文',80);
这是测试的数据你看能帮我实现下吗?我给你加分,因为我的结果还是显示字符串,而不是数据
then Result end) [' , Subject , ']')
from (select distinct Subject from sms2) as a; 原来mysql也支持这样写..学习.
liangck为什么你的“;”我就输出结束了啊
将语句终止符转换了吗?
DELIMITER $$DROP PROCEDURE IF EXISTS `test`.`proc_sms2`$$CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_sms2`()
BEGIN
DECLARE v_sql varchar(200);
DECLARE SQL1 VARCHAR(200);
SET @SQL1='';
set v_sql=concat('select name as 姓名','');
select @SQL1:= CONCAT(@SQL1,' , sum(case Subject when ''' , Subject , '''
then SCORE end) AS `' , Subject , '`')
from (select distinct Subject from STUDENT) as a;
SELECT @SQL1;
SET @v_sql = CONCAT(V_SQL,@sql1 , ' from sTUDENT group by name');
SELECT @V_SQL;
PREPARE stmt2 FROM @v_sql;
EXECUTE stmt2;
END$$DELIMITER ;
精简:
DELIMITER $$DROP PROCEDURE IF EXISTS `test`.`proc_sms2`$$CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_sms2`()
BEGIN
SET @SQL1='';
select @SQL1:= CONCAT(@SQL1,' , sum(case Subject when ''' , Subject , '''
then SCORE end) AS `' , Subject , '`')
from (select distinct Subject from STUDENT) as a;
SET @v_sql = CONCAT('select name as 姓名',@sql1 , ' from sTUDENT group by name');
PREPARE stmt2 FROM @v_sql;
EXECUTE stmt2;
END$$DELIMITER ;
BEGIN
SET @SQL1='';
select @SQL1:= CONCAT(@SQL1,' , sum(case Subject when ''' , Subject , '''
then SCORE end) AS `' , Subject , '`')
from (select distinct Subject from STUDENT) as a;
SET @v_sql = CONCAT('select name as 姓名',@sql1 , ' from sTUDENT group by name');
PREPARE stmt2 FROM @v_sql;
EXECUTE stmt2;
END$$DELIMITER ;这个吗?应该只有一个结果集,你检查一下是否有多个结果集
Array ( [0] => Array ( [0] => , sum(case Subject when 'yuwen' then SCORE end) AS `yuwen` [@SQL1:= CONCAT(@SQL1,' , sum(case Subject when ''' , Subject , ''' then SCORE end) AS `' , Subject , '`')] => , sum(case Subject when 'yuwen' then SCORE end) AS `yuwen` ) [1] => Array ( [0] => , sum(case Subject when 'shuxue' then SCORE end) AS `shuxue` [@SQL1:= CONCAT(@SQL1,' , sum(case Subject when ''' , Subject , ''' then SCORE end) AS `' , Subject , '`')] => , sum(case Subject when 'shuxue' then SCORE end) AS `shuxue` ) [2] => Array ( [0] => , sum(case Subject when 'yingyu' then SCORE end) AS `yingyu` [@SQL1:= CONCAT(@SQL1,' , sum(case Subject when ''' , Subject , ''' then SCORE end) AS `' , Subject , '`')] => , sum(case Subject when 'yingyu' then SCORE end) AS `yingyu` ) )