mysql一维转二维数据

例子数据

DROP TABLE IF EXISTS tmp0103.temp_test;
CREATE TABLE tmp0103.temp_test (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `class` VARCHAR(255) DEFAULT NULL,
  `score` DOUBLE DEFAULT NULL,
  `userid` INT(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1;  /*创建表结构*/

INSERT INTO tmp0103.temp_test VALUES ('1', 'math', '90', '1');
INSERT INTO tmp0103.temp_test VALUES ('2', 'english', '90', '1');
INSERT INTO tmp0103.temp_test VALUES ('3', 'computer', '80', '1');
INSERT INTO tmp0103.temp_test VALUES ('4', 'sports', '90', '1');
INSERT INTO tmp0103.temp_test VALUES ('5', 'math', '80', '2');
INSERT INTO tmp0103.temp_test VALUES ('6', 'english', '85', '2');
INSERT INTO tmp0103.temp_test VALUES ('7', 'computer', '100', '2');

通过定义变量存储临时结果

/*以下如果在mysql客户端，需要一条一条执行*/
SET @EE='';  /*定义变量EE为空*/
    SELECT @EE:=CONCAT(@EE,'SUM(IF(class=\'',class,'\'',',score,0)) AS ',class,',')   
    FROM (SELECT DISTINCT class FROM tmp0103.temp_test) A;  /*\'为在引号里面的\是对'的转义*/
SET @QQ=CONCAT('SELECT tmp0103.temp_test.userid AS columnA,',LEFT(@EE,LENGTH(@EE)-1),'   
    FROM tmp0103.temp_test GROUP BY userid');  /*LEFT(@EE,LENGTH(@EE)-1)，去掉EE中最后一句的最后一个,*/
PREPARE stmt2 FROM @QQ; /*预处理需要执行的动态SQL，其中stmt是一个变量*/
execute stmt2;  /*执行SQL语句*/
deallocate prepare stmt;  /*释放掉预处理段*/