发表时间：2014-03-07  
引用 收藏

行列转换的存储过程
初始图：

 

效果图：



 

实现过程：

1：建表、建数据

CREATE TABLE `changeprice` (   

   `id` bigint(20) NOT NULL auto_increment, 

   `sid` bigint(20) NOT NULL,

    `datecreated` timestamp NOT NULL default CURRENT_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');

 

存储过程实现：

Java代码 复制代码  收藏代码
  1. CREATE DEFINER=`root`@`localhost` PROCEDURE `test`()   
  2. begin     
  3.              DECLARE done int default 0;     
  4.              DECLARE strDate DATE;     
  5.              DECLARE str varCHAR(10000) default '';     
  6.         DECLARE cur1 CURSOR FOR select DISTINCT(DATE(datecreated)) from changeprice order by datecreated;     
  7.              DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;     
  8.              set done = 0;   
  9.              open cur1;     
  10.              REPEAT     
  11.              FETCH cur1 INTO strDate;     
  12.              if  done <> 1 then     
  13.              set str=CONCAT(str, ',', 'SUM(if(datecreated=''', strDate, ''', price, 0))', '''', strDate,'''');     
  14.              end IF;     
  15.              UNTIL done = 1  
  16.              END REPEAT;     
  17.              close cur1;   
  18.              set @sqlString=CONCAT(' select sid ', str, ' from changeprice group by sid ');     
  19.              prepare sqlstmt from @sqlString;     
  20.              execute sqlstmt;     
  21.              deallocate prepare sqlstmt;     
  22.              set str='';   
  23.     end;  
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;

 

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