mysql 动态行转列

前言： mysql的行转列并没有mssql中的pivot

 

测试数据：

DROP TABLE IF EXISTS `mytest`;
CREATE TABLE `mytest` (
  `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 `mytest` VALUES ('1', 'math', '90', '1');
INSERT INTO `mytest` VALUES ('2', 'english', '90', '1');
INSERT INTO `mytest` VALUES ('3', 'computer', '80', '1');
INSERT INTO `mytest` VALUES ('4', 'sports', '90', '1');
INSERT INTO `mytest` VALUES ('5', 'math', '80', '2');
INSERT INTO `mytest` VALUES ('6', 'english', '85', '2');
INSERT INTO `mytest` VALUES ('7', 'computer', '100', '2');

 

语句：

SET @EE='';
    SELECT @EE:=CONCAT(@EE,'SUM(IF(class=\'',class,'\'',',score,0)) AS ',class,',') 
    FROM (SELECT DISTINCT class FROM mytest) A;
SET @QQ=CONCAT('SELECT mytest.userid AS columnA,',LEFT(@EE,LENGTH(@EE)-1),' 
    FROM mytest GROUP BY userid');
PREPARE stmt2 FROM @QQ;

EXECUTE stmt2;

 

原数据：

​

效果：

​

 

PS: 如果将这块sql封装成存储过程，会返回2个结果集，一个sql语句，还有一个查询结果。
所以如果是存储过程的话，推荐这么实用：

#drop PROCEDURE sp_mytest

create PROCEDURE sp_mytest()
begin 
SET @EE='';
    SELECT b.* into @EE from ( SELECT @EE:=CONCAT(@EE,'SUM(IF(class=\'',class,'\'',',score,0)) AS ',class,',') col
    FROM (SELECT DISTINCT class FROM mytest) A) b ORDER BY b.col desc limit 0,1 ;
SET @QQ=CONCAT('SELECT mytest.userid AS columnA,',LEFT(@EE,LENGTH(@EE)-1),' 
    FROM mytest GROUP BY userid');
PREPARE stmt2 FROM @QQ;
EXECUTE stmt2;
end

call sp_mytest()