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 ;

本文出自 “用户和存储过程” 博客，请务必保留此出处http://9548010.blog.51cto.com/9538010/1624691

mysql行列调换

原文：http://9548010.blog.51cto.com/9538010/1624691