Mysql存储过程－行列转换

在网上找了些资料，自己动手写了个行列转换的存储过程。
下面片段为表结构：

CREATE TABLE `changeprice` (
  `id` bigint(20) NOT NULL auto_increment,
  `sid` bigint(20) NOT NULL,
  `datecreated` Date NOT NULL default CURRENT_TIMESTAMP,
  `price` varchar(50) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

表中插入以下数据：
(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');
现要显示为：
sid，5-8，5-9，5-10，5-11，5-12...
1， 30， 0，  50，12， 20 ...
2， 30， 50， 0， 12， 0 ...
3， 0，  0，  0，  0， 12 ...
采用mysql存储过程加上cursor实现。

create procedure test111()
begin
         DECLARE done int default 0;
         DECLARE strDate DATE;
         DECLARE str CHAR(1000) default '';
	DECLARE cur1 CURSOR FOR select DISTINCT(DATE(datecreated)) from changeprice order by datecreated;
         DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;
         open cur1;
         REPEAT
         if not done then
         FETCH cur1 INTO strDate;
         set str=CONCAT(str, ',', 'SUM(if(datecreated=''', strDate, ''', price, 0))', '''', strDate,'''');
         end IF;
         UNTIL done END REPEAT;
         set @sqlString=CONCAT(' select sid ', str, ' from changeprice group by sid ');
         prepare sqlstmt from @sqlString;
         execute sqlstmt;
         deallocate prepare sqlstmt;

end

评论

2 楼 openopenjava 2012-06-20  

后面总是加一列是怎么回事？

1 楼 winner720 2009-09-15  

真是太感谢你了，你这个对我有很大的帮助。