mysql存储 队列_MYSQL 数据队列交换 存储过程实现

行列转换的存储过程

初始图：

效果图：

实现过程：

1：建表、建数据

CREATE TABLE `changeprice` (

`id` bigint(20) NOT NULL auto_increment,

`sid` bigint(20) NOT NULL,

`datecreated` timestamp NOT NULL defaultCURRENT_TIMESTAMP,

`price` varchar(50) NOT NULL,

PRIMARY KEY  (`id`)

) ;

插入数据：

(1,1,'2009-05-08','30'),

(2,1,'2009-05-10','50'),

(3,1,'2009-05-11','12'),

(4,1,'2009-05-12','20'),

(5,1,'2009-05-14','50'),

(6,1,'2009-05-15','30'),

(7,3,'2009-05-11','12'),

(8,3,'2009-05-12','30'),

(9,3,'2009-05-14','50'),

(10,3,'2009-05-15','30'),

(11,2,'2009-05-08','30'),

(12,2,'2009-05-09','50'),

(13,2,'2009-05-11','12'),

(14,2,'2009-05-13','20'),

(15,2,'2009-05-14','50'),

(16,2,'2009-05-15','30');

存储过程实现：

CREATE DEFINER=`root`@`localhost` PROCEDURE `test`() begin DECLARE done int default 0; DECLARE strDate DATE; DECLARE str varCHAR(10000) default ''; DECLARE cur1 CURSOR FOR select DISTINCT(DATE(datecreated)) from changeprice order by datecreated; DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1; set done = 0; open cur1; REPEAT FETCH cur1 INTO strDate; if done <> 1 then set str=CONCAT(str, ',', 'SUM(if(datecreated=''', strDate, ''', price, 0))', '''', strDate,''''); end IF; UNTIL done = 1 END REPEAT; close cur1; set @sqlString=CONCAT(' select sid ', str, ' from changeprice group by sid '); prepare sqlstmt from @sqlString; execute sqlstmt; deallocate prepare sqlstmt; set str=''; end;

欢迎大家阅读《MYSQL 数据队列交换 存储过程实现》,跪求各位点评，by 搞代码

编辑器运行 call test();即可