mysql动态获取列（使用存储过程版）

1.先看sql(创建一个存储过程的实际代码)

第一行的“root”是你mysql的用户名、“p_test2”是函数名

CREATE DEFINER=`root`@`localhost` PROCEDURE `p_test2`()
BEGIN
	SET @num1 = NULL;/*定义变量*/
	SET SESSION group_concat_max_len =1024*10; /*为拼接字符的函数设置最大长度*/
	SELECT
	  GROUP_CONCAT(DISTINCT  /*将去重后的所有的name逐一放在“sum (case when ...)”中，再将期接起来*/
	    CONCAT(
	      'SUM(CASE WHEN t2.name = \'',    /*使用了转义符斜杠*/
				t2.name , 
				'\' THEN t2.amount else 0 END) AS ',
	      t2.name
	    )
	  ) INTO @num1  /*赋值到变量*/
	FROM 
	   test2 t2;    /*至此，一串自动sum(case when ......)的语句拼接完成*/
		 
	SET @sql = CONCAT(   /*定义@sql进行接收*/
		 ' select ',
		 ' t2.time as time ,',
		 @num1,   /*此时的@num就是一串许多个“sum(case when ......)”的语句了*/
		 ' from test2 t2 ',
		 ' GROUP BY t2.time '
	);   
	#select @sql;  /*可以将这一句解开注释后全选这整段代码运行查看最终的sql语句*/
	PREPARE stmt FROM @sql;  /*然后就是执行了*/
	EXECUTE stmt;
	DEALLOCATE PREPARE stmt;
  END

2.创建过程

新建查询，全选粘贴，选中后右键“运行已选择的”，注意看下面要出“ok”字样才行。

成功后在函数列表里面会出现这个函数“p_test2”

附上建表语句

DROP TABLE IF EXISTS `test2`;
CREATE TABLE `test2`  (
  `id` int(10) NOT NULL AUTO_INCREMENT COMMENT 'id不可重复，唯一标识，不能为空',
  `name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '名称',
  `amount` int(20) NULL DEFAULT NULL COMMENT '数量',
  `time` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 12 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

INSERT INTO `test2` VALUES (1, 'card', 10, '2021-01');
INSERT INTO `test2` VALUES (2, 'card', 10, '2021-01');
INSERT INTO `test2` VALUES (3, 'cash', 20, '2021-01');
INSERT INTO `test2` VALUES (4, 'cheque', 20, '2021-02');
INSERT INTO `test2` VALUES (5, 'cash', 10, '2021-03');
INSERT INTO `test2` VALUES (6, 'card', 20, '2021-04');
INSERT INTO `test2` VALUES (7, 'card', 10, '2021-05');
INSERT INTO `test2` VALUES (8, 'cash', 20, '2021-02');
INSERT INTO `test2` VALUES (9, 'cash', 10, '2021-03');
INSERT INTO `test2` VALUES (10, 'cash', 20, '2021-04');
INSERT INTO `test2` VALUES (11, 'zhifubao', 12, '2021-06');

表是这样的

表建好后，执行这个函数，使用 call 关键字调用函数名称

如果将上面函数代码中的#select @sql注释打开的话，就会有两个结果集，第一个就是拼接成的完整sql，可以选中将其全选复制出来看看完整的sql长什么样。

结果集2就是运行这个完整sql后得到结果，如下图

3.需求分析

现在有一张支付方式记录表，记录了每次支付时的支付方式、支付金额、支付时间。
现在需要查询出每个月份中每种支付方式支付的总金额。
查询出来的结果的格式是，第一列是时间，第二列是支付方式1，第三列是支付方式2，第n列是支付方式n-1.

很明显的是，支付方式的种类是不确定的。
我们一般在处理这种问题时，使用的就是如同下面的sql：

select 
    时间,
    sum(case when 支付方式='aaa' then 金额 else 0 end) as aaa,
    sum(case when 支付方式='bbb' then 金额 else 0 end) as bbb,
    sum(case when 支付方式='ccc' then 金额 else 0 end) as ccc,
    sum(case when 支付方式='ddd' then 金额 else 0 end) as ddd,
    sum(case when 支付方式='eee' then 金额 else 0 end) as eee
from 
    表
group by
    时间

即我们已经知道支付方式的种类了，所以才可以使用case when，如果不知道支付方式种类呢？
或者目前的支付方式你是知道的，未来有新增了支付方式的种类，表中的name值不再只有例如cash、card等等的了，又多了个xxx，怎么办？。即直接使用case when就是在对已知的历史数据做枚举法，不能对未来的数据做准备。

这个问题的核心在于每次多一个支付方式时sql语句中的case when又要增加一个，还要再修改项目代码。那就不好。

使用存储过程则可以自动地将所有支付方式遍历出来，也不用知道表中有什么种类地支付方式，即未来不用修改sql语句了。