mysql行列调换方法

it2025-03-03 23

行变列，列变行

财务样式模板：

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 DOSET 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 ;

转载于:https://www.cnblogs.com/youhunyimeng/p/4368279.html