MySQL 通过存储过程，进行行列转换，列标题从指定表中读取

原始数据如下：

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', '西瓜');

 

 

其中通过tbl_type.id =  tbl_test.type 关联

统计给每个月份，每种typename的数量

结果如下：

 

 

存储过程如下：

## 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

如非使用存储过程，则需要把tbl_type中的数据全部固定写死，如下sql：

SELECT 
    month(d.ctime) as '月份', 
		sum(if (d.type = 1, d.money, 0 )) AS '苹果', 
		sum(if (d.type = 2, d.money, 0 )) AS '香蕉',
		sum(if (d.type = 3, d.money, 0 )) AS '橙子',
		sum(if (d.type = 4, d.money, 0 )) AS '葡萄',
		sum(if (d.type = 5, d.money, 0 )) AS '梨',
		sum(if (d.type = 6, d.money, 0 )) AS '柠檬',
		sum(if (d.type = 7, d.money, 0 )) AS '桃子',
		sum(if (d.type = 8, d.money, 0 )) AS '西瓜' 
FROM 
    tbl_test  d
GROUP BY 
month(d.ctime) ;

 

所得结果：

 

 

其他：

select "select name" 
union 
select concat(", sum(case when datediff(ctime,'",date(ctime),"')=0 then money else 0 end) as '", 
date(ctime),"' ") a from tbl_test group by a 
union 
select " from tbl_test group by name;";

生成：
 

select name
, sum(case when datediff(ctime,'2016-01-01')=0 then money else 0 end) as '2016-01-01' 
, sum(case when datediff(ctime,'2016-02-01')=0 then money else 0 end) as '2016-02-01' 
, sum(case when datediff(ctime,'2016-05-02')=0 then money else 0 end) as '2016-05-02' 
, sum(case when datediff(ctime,'2016-08-03')=0 then money else 0 end) as '2016-08-03' 
 from tbl_test group by name;

 

运行结果：