mysql 动态行转列多表怎么实现？

Question

表1

表2

现有2张表：表1，表2。

表2为动态列与表1的courseid对应

列是根据表2动态生成的，不是固定的。表2有什么列，就生成什么列

查询统计表1相同tags的watchtime合计数 lonigid为索引。

想要的最终效果：

|用户名 | 法律法规          | 工程经济          | 建筑实务          | 市政实务          | 
|gf1001 | wacthtime合计 | wacthtime合计 | wacthtime合计 | wacthtime合计 | 
|gf1002 | wacthtime合计 | wacthtime合计 | wacthtime合计 | wacthtime合计 | 
|gf1003 | wacthtime合计 | wacthtime合计 | wacthtime合计 | wacthtime合计 | 

Answer 1

表tbl_test

CREATE TABLE `tbl_test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL,
  `type` int(11) DEFAULT NULL,
  `money` int(11) DEFAULT NULL,
  `ctime` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;
 
INSERT INTO tbl_test (id, name, type, money, ctime) VALUES ('1', 'mike', '1', '6', '2016-01-01 12:58:00');
INSERT INTO tbl_test (id, name, type, money, ctime) VALUES ('2', 'mike', '2', '10', '2016-02-01 13:52:56');
INSERT INTO tbl_test (id, name, type, money, ctime) VALUES ('3', 'leo', '3', '10', '2016-05-02 00:05:05');
INSERT INTO tbl_test (id, name, type, money, ctime) VALUES ('4', 'mike', '1', '6', '2016-08-03 08:06:05');
INSERT INTO tbl_test (id, name, type, money, ctime) VALUES ('5', 'mike', '5', '9', '2016-01-01 12:58:00');
INSERT INTO tbl_test (id, name, type, money, ctime) VALUES ('6', 'mike', '8', '15', '2016-01-01 12:58:00');

表tbl_type

CREATE TABLE `tbl_type` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `typename` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;
INSERT INTO tbl_type (id, typename) VALUES ('1', '苹果');
INSERT INTO tbl_type (id, typename) VALUES ('2', '香蕉');
INSERT INTO tbl_type (id, typename) VALUES ('3', '橙子');
INSERT INTO tbl_type (id, typename) VALUES ('4', '葡萄');
INSERT INTO tbl_type (id, typename) VALUES ('5', '梨');
INSERT INTO tbl_type (id, typename) VALUES ('6', '柠檬');
INSERT INTO tbl_type (id, typename) VALUES ('7', '桃子');
INSERT INTO tbl_type (id, typename) VALUES ('8', '西瓜');

存储过程，行列转换，动态获取列标题，列标题从指定表中读取

## SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY','')); 
 
DROP PROCEDURE IF EXISTS test_proc;  
CREATE PROCEDURE test_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, typename FROM tbl_type ORDER BY id; 
    ## 为下面while循环建立一个退出标志，当游标遍历完后将flag的值设置为1
    DECLARE continue handler for not found SET flag = 1;
 
    SET @esql = 'SELECT CONCAT(LPAD(MONTH(d.ctime), 2, 0), \'月\')  AS \'月份\' ' ;
    -- SET @esql = 'SELECT  month(d.ctime) as \'月份\' ' ;
    
    ## 打开游标
    OPEN t_list;
    ## 将游标中的值赋给定义好的变量，实现for循环的要点
    FETCH t_list INTO  tid, tname;
    WHILE flag <> 1 DO
        ## SELECT  tid, tname;
        SET @tsql = ', sum(if (d.type = ' + tid+ ', d.money, 0 )) AS \'' + tname+ '\''  ;
    ## SELECT @tsql;
    SET @esql = concat(@esql, ', sum(if (d.type = ' , tid , ', d.money, 0 )) AS \'' , tname, '\'' ) ;
    FETCH t_list INTO  tid, tname;
 
    END WHILE ;
    ## 关闭游标
    CLOSE t_list ;
    SET @esql = CONCAT(@esql ,' FROM  tbl_test  d  GROUP BY   month(d.ctime) ;') ;
 
    PREPARE stmt FROM @esql;-- 预编译一条sql语句，并命名为stmt
    EXECUTE stmt;-- 执行预编译sql
 
    ## 拼接完成后可以调用 select @s 语句，查看最终拼接的sql语句是否正确
    ##select @esql;
END