在网上找了些资料,自己动手写了个行列转换的存储过程。
下面片段为表结构:
表中插入以下数据:
(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 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