mysql行列调换

32ew12

财务样式模板


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 ;