mysql 行专列

1基表：

CREATE TABLE `feerate` (
  `id` bigint(20) DEFAULT NULL COMMENT '编号',
  `stagenum` int(11) DEFAULT '0' COMMENT '期数',
  `feerate` decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT '费率',
  `discount` decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT '折扣',
  `standardl` decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT '折扣基准低',
  `standardh` decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT '折扣基准高',
  `isDiscount` int(11) NOT NULL DEFAULT '1' COMMENT '是否有折扣(1 有,0 没有，默认有)',
  `iscontainl` int(2) DEFAULT '0' COMMENT '折扣基准低是否包含(0 不包含,1 包含)',
  `iscontainh` int(2) DEFAULT '0' COMMENT '折扣基准高是否包含(0 不包含,1 包含)'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

２.数据格式：
insert  into `feerate`(`id`,`stagenum`,`feerate`,`discount`,`standardl`,`standardh`,`isDiscount`,`iscontainl`,`iscontainh`) values (1,3,'0.75','1.00','0.00','0.00',0,0,0),(2,3,'0.68','0.90','0.01','50000.00',1,0,0),(3,3,'0.64','0.85','50000.01','99999.99',1,0,0),(4,3,'0.60','0.80','100000.00','10000000.00',1,1,0),(28,6,'0.70','1.00','0.00','0.00',0,0,0),(29,6,'0.63','0.90','0.01','50000.00',1,0,0),(30,6,'0.60','0.85','50000.01','99999.99',1,0,0),(31,6,'0.56','0.80','100000.00','10000000.00',1,0,0),(32,12,'0.60','1.00','0.00','0.00',0,0,0),(33,12,'0.54','0.90','0.01','50000.00',1,0,0),(34,12,'0.51','0.85','50000.01','99999.99',1,0,0),(35,12,'0.48','0.80','100000.00','10000000.00',1,0,0),(36,18,'0.60','1.00','0.00','0.00',0,0,0),(37,18,'0.54','0.90','0.01','50000.00',1,0,0),(38,18,'0.51','0.85','50000.01','99999.99',1,0,0),(39,18,'0.48','0.80','100000.00','10000000.00',1,0,0),(40,24,'0.62','1.00','0.00','0.00',0,0,0),(41,24,'0.56','0.90','0.01','50000.00',1,0,0),(42,24,'0.53','0.85','50000.01','99999.99',1,0,0),(43,24,'0.50','0.80','100000.00','10000000.00',1,0,0);

３.存储过程

DELIMITER $$

/*!50003 CREATE DEFINER=`root`@`%` PROCEDURE `testft`()
BEGIN
    declare v_sql varchar(1000);
    declare v_name varchar(50);
    declare v_coname varchar(50);
   
    declare c_result cursor for
         SELECT  DISTINCT discount ,IF(discount='0.00','基准费率',CONCAT("'",discount*10 ,'折',"'")) FROM feerate;
         DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET v_name = null;
     set v_sql='select stagenum 基准 ';
     open c_result;
     fetch c_result into v_name ,v_coname;
     while(v_name is not null) do
     set v_sql := concat(v_sql,',max(case when discount=',v_name,' then feerate else 0',' end)',v_coname);
     fetch c_result into v_name,v_coname;
     end while;
     close c_result;
    
     set v_sql = concat('create  table temp_feerate  as ',v_sql,' from feerate group by stagenum');
     DROP TABLE IF EXISTS `tmp_feerate`;
     set @v_sql=v_sql;   
     prepare stmt from @v_sql; 
     EXECUTE stmt;     
     deallocate prepare stmt;
    END */$$
DELIMITER ;

 

４执行结果表：

５用ＳＱＬｙｏｇ执行存储过程：ｃａｌｌ　‘存储过程名字’；