表依据对应字段生成其它三个字段

表根据对应字段生成其它三个字段

根据一张表的两个时间的字段，根据对应字段生成其它三个字段，通过表的自联接可以做得到，但是会出现些意想不到的问题，最终解决的办法---通过存储过程。

数据库表
CREATE TABLE `box_mobile_log_active_` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `boxId` int(11) NOT NULL,
  `channelId` int(11) NOT NULL,
  `fatherChannelId` int(11) NOT NULL,
  `appId` int(11) NOT NULL,
  `cpId` int(11) NOT NULL,
  `active_day` datetime NOT NULL,
  `createTime` datetime DEFAULT NULL,
  `active_num` int(11) NOT NULL,
  `theDay` int(11) DEFAULT '0',
  `secondDay` int(11) DEFAULT '0',
  `thirdDay` int(11) DEFAULT '0',
  `active_num_rate` decimal(10,4) NOT NULL DEFAULT '0.0000',
  `province` varchar(80) NOT NULL,
  `city` varchar(80) NOT NULL,
  `updateTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=113234 DEFAULT CHARSET=utf8;

存储过程：

use box_oms;
CREATE  PROCEDURE `sp_box_mobile_active_log_test`(IN v_day int)
BEGIN
	 DECLARE v_done1 INT DEFAULT 0;
	 DECLARE  v_theDay,v_secondDay,v_thirdDay,v_active_num,v_active_num_rate  INT DEFAULT 0;
   DECLARE  v_boxId,v_channelId,v_fatherChannelId,v_appId,v_cpId INT;
   DECLARE  v_createTime,v_active_day varchar(10);
   DECLARE  v_province,v_city varchar(20);

	 DECLARE v_activeList CURSOR FOR SELECT a.boxId boxId,c.id channelId,c.fatherId fatherChannelId,a.appId appId,d.cpId cpId,DATE(a.installTime),DATE(a.updateTime) active_day,COUNT(a.id) active_num,COUNT(a.id) active_num,a.province province,a.city city 
   FROM box_mobile_log_sum a,box_market.channel_box b,box_market.channel_box_chinfo c,box_market.res_app d 
   WHERE a.appId = d.id AND a.boxId = b.boxId AND b.channelId=c.id  GROUP BY DATE(a.installTime),DATE(a.updateTime),a.boxId,a.appId;
	  DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done1 = 1;
		
		
		  INSERT IGNORE INTO box_mobile_log_sum SELECT * FROM box_log.box_mobile_log a where a.updateTime>DATE_SUB(CURDATE(),INTERVAL v_day DAY) AND a.updateTime<=DATE_SUB(CURDATE(),INTERVAL -1 DAY);
      CALL sp_filter(2);
		-- CALL sp_rehab_mobile_active_installday;
		-- 生成激活统计表
	TRUNCATE TABLE box_mobile_log_active_; 
   OPEN v_activeList;
	 REPEAT
			FETCH v_activeList INTO v_boxId,v_channelId,v_fatherChannelId,v_appId,v_cpId,v_createTime,v_active_day,v_active_num,v_active_num_rate,v_province,v_city;
	 IF v_done1 != 1 THEN
     IF to_days(v_createTime)=to_days(v_active_day) THEN
      SET v_theDay=v_active_num;
     ELSEIF (to_days(v_createTime)+1)=to_days(v_active_day) THEN
      SET v_secondDay=v_active_num;
     ELSEIF (to_days(v_createTime)+2)=to_days(v_active_day) THEN
      SET v_thirdDay=v_active_num;
    END IF;
    INSERT INTO box_mobile_log_active_ (boxId,channelId,fatherChannelId,appId,cpId,createTime,active_day,active_num,active_num_rate,province,city,theDay,secondDay,thirdDay)
     VALUES(v_boxId,v_channelId,v_fatherChannelId,v_appId,v_cpId,v_createTime,v_active_day,v_active_num,v_active_num_rate,v_province,v_city,v_theDay,v_secondDay,v_thirdDay);       
     SET v_theDay=0;
     SET v_secondDay=0;
     SET v_thirdDay=0;
   END IF;
	 UNTIL v_done1 = 1	END REPEAT;
	 CLOSE v_activeList;	
	 COMMIT;	
END

表依据对应字段生成其它三个字段

相关推荐