存储过程Case

存储过程（MySQL）
数据准备

CREATE TABLE `km` (
  `id` int NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `idpath` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ;

INSERT INTO `km` VALUES (1, '收入', '0');
INSERT INTO `km` VALUES (2, '支出', '0');
INSERT INTO `km` VALUES (3, '内部收入', '0-1');
INSERT INTO `km` VALUES (4, '服务收入', '0-1-3');
INSERT INTO `km` VALUES (5, '演讲', '0-1-3-4');
INSERT INTO `km` VALUES (6, '成本', '0-2');
INSERT INTO `km` VALUES (7, '家具采购', '0-2-6');
INSERT INTO `km` VALUES (8, '课桌', '0-2-6-8');
INSERT INTO `km` VALUES (9, '椅子', '0-2-6-8');

根据idpath动态生成数据列

CREATE PROCEDURE `km_procedure`()
Begin
	Declare len Int;  -- 要增加的列数
	Declare J Int DEFAULT 1;
	Declare v_sql Varchar(500) default '';

	Select max(LENGTH(Replace(idpath,'-',''))) into len From km; -- 获取idpath的最大长度作为列数
	-- 创建临时表
	drop table if exists tmp_table;
	Create TEMPORARY Table if not exists tmp_table(select id,name,idpath from km);
	-- 动态增加列
	set J = 1;
	set v_sql = '';
	While J<=len DO
		set v_sql = concat('Alter Table tmp_table Add km',J,' Varchar(100);');
		Set J=J+1;
		set @p_sql = v_sql;
		prepare stmt from @p_sql; -- 预处理sql语句
		EXECUTE stmt;                  -- 执行sql语句
	END WHILE;
    -- 给动态增加的列赋值
	set J = 1;
	set v_sql = '';
	While J<=len DO
		set v_sql = concat('update tmp_table set km',J,
			'=substring_index(substring_index(idpath,''-'',', J,'), ''-'', -1) where (length(idpath)-length(Replace(idpath,''-'',''''))) >= ',J-1);
		set @p_sql = v_sql;
		prepare stmt from @p_sql; 
		EXECUTE stmt;
		Set J=J+1;
	END WHILE;
	deallocate prepare stmt; -- 释放预处理的sql语句
	SELECT * from tmp_table;
End

效果图：