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
郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。
