mysql 列交换_mysql行列调换方法

行变列，列变行

财务样式模板：

CREATE TABLE `grade` (

`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,

`name` VARCHAR(250) DEFAULT NULL,

`course` VARCHAR(250) DEFAULT NULL,

`score` DECIMAL(20,2) DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=INNODB AUTO_INCREMENT=670 DEFAULT CHARSET=utf8

CREATE TABLE `caiwu` (

`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,

`name` VARCHAR(250) DEFAULT NULL,

`Time` VARCHAR(250) DEFAULT NULL,

`AMOUNT` DECIMAL(20,2) DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=INNODB DEFAULT CHARSET=utf8

DELIMITER $$

USE `test`$$

DROP PROCEDURE IF EXISTS `sp_amount`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_amount`()

BEGIN

#消费时间

DECLARE Time_n VARCHAR(250);

#消费类型数量

DECLARE COUNT INT;

#计数器

DECLARE i INT DEFAULT 0;

#拼接SQL字符串

SET @s = 'SELECT name';

SET COUNT = (SELECT COUNT(DISTINCT time) FROM caiwu);

WHILE i < COUNT DO

SET time_n = (SELECT DISTINCT Time FROM caiwu ORDER BY Time LIMIT i,1);

SET @s = CONCAT(@s, ', SUM(CASE Time WHEN ','\'', Time_n,'\'',' THEN amount END )',' AS ','\'',time_n,'\'');

SET i = i+1;

END WHILE;

SET @s = CONCAT(@s, ' FROM caiwu GROUP BY name');

#用于调试

#SELECT @s;

PREPARE stmt FROM @s;

EXECUTE stmt;

END$$

DELIMITER ;