MySQL存储过程的动态行转列

表1

DROP TABLE IF EXISTS `tuser`;
CREATE TABLE `tuser`  (
  `loginid` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `watchtime` double NULL DEFAULT NULL,
  `courseid` int(0) NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

INSERT INTO `tuser` VALUES ('L1', 0.56, 2);
INSERT INTO `tuser` VALUES ('L1', 0.66, 2);
INSERT INTO `tuser` VALUES ('L1', 0.66, 2);
INSERT INTO `tuser` VALUES ('L2', 0.57, 2);
INSERT INTO `tuser` VALUES ('L2', 0.57, 2);
INSERT INTO `tuser` VALUES ('L2', 0.57, 1);

表2

DROP TABLE IF EXISTS `t1`;
CREATE TABLE `t1`  (
  `id` int(0) NOT NULL AUTO_INCREMENT,
  `course` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

INSERT INTO `t1` VALUES (1, '科目1');
INSERT INTO `t1` VALUES (2, '科目2');
INSERT INTO `t1` VALUES (3, '科目3');

存储过程行转列

DROP PROCEDURE IF EXISTS t_proc;  
CREATE PROCEDURE t_proc() 
BEGIN
    DECLARE esql VARCHAR(4000) ;
    DECLARE tid INT;
    DECLARE tname VARCHAR(50);
    DECLARE flag INT DEFAULT 0;
 
    ## 定义一个游标来记录sql查询的结果
    DECLARE t_list CURSOR FOR select id,course from t1 ORDER BY id; 
    ## 为下面while循环建立一个退出标志，当游标遍历完后将flag的值设置为1
    DECLARE continue handler for not found SET flag = 1;
 
    SET @esql = 'SELECT d.loginid  AS \'loginid\' ' ;
    
    ## 打开游标
    OPEN t_list;
    ## 将游标中的值赋给定义好的变量，实现for循环的要点
    FETCH t_list INTO  tid, tname;
    WHILE flag <> 1 DO
        ## SELECT  tid, tname;
        SET @tsql = ', ROUND(sum(if (d.courseid = ' + tid+ ', d.watchtime, 0 )),2) AS \'' + tname+ '\''  ;
    ## SELECT @tsql;
    SET @esql = concat(@esql, ', ROUND(sum(if (d.courseid = ' , tid , ', d.watchtime, 0 )),2) AS \'' , tname, '\'' ) ;
    FETCH t_list INTO  tid, tname;
 
    END WHILE ;
    ## 关闭游标
    CLOSE t_list ;
    SET @esql = CONCAT(@esql ,' FROM  tuser  d  GROUP BY d.loginid;') ;
 
    PREPARE stmt FROM @esql;-- 预编译一条sql语句，并命名为stmt
    EXECUTE stmt;-- 执行预编译sql
 
    ## 拼接完成后可以调用 select @s 语句，查看最终拼接的sql语句是否正确
    ##select @esql;
END

调用结果

调用存储过程：call t_proc();

查看存储过程执行的sql：select @esql;

普通行转列

SELECT loginid,
  sum(
  CASE
    WHEN courseid='1'
    THEN watchtime
    ELSE 0
  END) AS "科目一",
  sum(
  CASE
    WHEN courseid='2'
    THEN watchtime
    ELSE 0
  END) AS "科目二",
  sum(
  CASE
    WHEN courseid='3'
    THEN watchtime
    ELSE 0
  END) AS "科目三"
FROM tuser
GROUP BY loginid