一个简单的行列转换,但在mysql5.0中搞不定,
CREATE PROCEDURE proc_sms2
(
)LABEL_PROC:
BEGIN
DECLARE v_sql varchar(200);
SET @v_sql = 'select Name as ' + '姓名';
select @v_sql = @sql + ' , sum(case Subject when ''' + Subject + '''
then Result end) [' + Subject + ']'
from (select distinct Subject from sms2) as a;
SET @v_sql = @v_sql + ' from sms2 group by name';
EXECUTE sl;
END LABEL_PROC
应该是语法的问题,请问大家知道mysql'中该怎么写吗谢谢
CREATE PROCEDURE proc_sms2
(
)LABEL_PROC:
BEGIN
DECLARE v_sql varchar(200);
SET @v_sql = 'select Name as ' + '姓名';
select @v_sql = @sql + ' , sum(case Subject when ''' + Subject + '''
then Result end) [' + Subject + ']'
from (select distinct Subject from sms2) as a;
SET @v_sql = @v_sql + ' from sms2 group by name';
EXECUTE sl;
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');
SET @sql = v_sql;
PREPARE sl FROM @sql;
EXECUTE sl;
END LABEL_PROC
我改了还是不行啊
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)