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

表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合计 | 

表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

这是一个行转列的问题，参照以下SQL

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 '项目管理',
  sum(case when courseid='4' then watchtime else 0 end) as '建筑实务',
  sum(case when courseid='5' then watchtime else 0 end) as '市政实务',
  sum(case when courseid='6' then watchtime else 0 end) as '公路实务',
  sum(case when courseid='7' then watchtime else 0 end) as '机电实务',
  sum(case when courseid='8' then watchtime else 0 end) as '铁路实务',
  sum(case when courseid='9' then watchtime else 0 end) as '民航实务'
from
    '表2'
GROUP BY
   loginid

若有帮助，谢谢采纳～

代码如下

SELECT temp.* ,GROUP_CONCAT(temp.course SEPARATOR '  : ') 课程,GROUP_CONCAT(temp.total SEPARATOR '  : ') 合计 from (
select table1.loginid,table2.*,SUM(table1.watchtime) total from table2 INNER JOIN table1 on table2.id = table1.courseid GROUP BY table1.loginid,table2.id) temp 
group by temp.loginid

效果图

然后你自己解析课程和合计中的内容即可

动态的行专列，单纯用sql语句没法实现的，只能借助存储过程之类的来实现

这个不管你是查出来用来做什么都可以用另外的办法变通，就直接表1关联表2，然后用loginid分组，用group_concat去拼接你要转列的字段；
优点：sql简单效率高；
缺点：查询出来的数据如果直接使用需要按照规则解析，或者重新封装一下；