mysql中,通过判断来更新本张表的数据

仔仔_杨 发布于 2014/06/09 16:48
阅读 426
收藏 0



表2中每天,每个user_id可能会有多条记录,求一个存储过程:


当表2的c_date _ 减表1的register_date 为1的时候 ,表1的second_day设置为1. 

当表2的c_date _ 减表1的register_date 为2的时候 ,表1的third_day设置为1. 


.... 

当表2的c_date _ 减表1的register_date 为7的时候 ,表1的seven_day设置为1.


当表1的register_date 为今天的时候,*_day为null;


谢谢.

表1:

DROP TABLE IF EXISTS `start_remain2`;
CREATE TABLE `start_remain2` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `user_id` bigint(20) DEFAULT NULL,
  `city_id` bigint(20) DEFAULT NULL COMMENT '城市ID',
  `user_type` int(11) DEFAULT NULL COMMENT '客户类型(1:乘客,2:司机,3:顾问)',
  `device_type` int(11) DEFAULT NULL COMMENT '平台(3:安卓,4:IOS)',
  `channel` varchar(255) DEFAULT NULL COMMENT '渠道号',
  `second_day` int(11) DEFAULT NULL COMMENT '次日是否登录',
  `third_day` int(11) DEFAULT NULL COMMENT '第3天是否登录',
  `four_day` int(11) DEFAULT NULL COMMENT '第4天是否登录',
  `five_day` int(11) DEFAULT NULL COMMENT '第5天是否登录',
  `six_day` int(11) DEFAULT NULL COMMENT '第6天是否登录',
  `seven_day` int(11) DEFAULT NULL COMMENT '第7天是否登录',
  `register_date` datetime DEFAULT NULL COMMENT '注册日期',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

表2:
DROP TABLE IF EXISTS log_function2;
CREATE TABLE log_function2 (
  id bigint(20) NOT NULL AUTO_INCREMENT,
  c_date datetime DEFAULT NULL,
  user_id bigint(20) DEFAULT NULL,
  function_key varchar(255) DEFAULT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

-- 测试数据
-- ----------------------------
-- Records of start_remain
-- ----------------------------
INSERT INTO `start_remain` VALUES ('1', '2', '1', '1', null, null, null, null, null, null, null, null,  '2014-03-12 10:37:34');
INSERT INTO `start_remain` VALUES ('2', '3', '2', '1', null, null, null, null, null, null, null, null,  '2014-03-12 13:46:59');
INSERT INTO `start_remain` VALUES ('3', '4', '2', '3', null, null, null, null, null, null, null, null,  '2014-03-12 16:55:05');
INSERT INTO `start_remain` VALUES ('4', '5', '2', '2', null, null, null, null, null, null, null, null,  '2014-03-12 17:47:15');
INSERT INTO `start_remain` VALUES ('5', '6', '2', '2', null, null, null, null, null, null, null, null,  '2014-03-12 17:47:46');
INSERT INTO `start_remain` VALUES ('6', '7', '2', '2', null, null, null, null, null, null, null, null,  '2014-03-12 17:52:13');






INSERT INTO `log_function2` (c_date,user_id,function_key) VALUES (  '2014-03-12 10:37:34',2,null);
INSERT INTO `log_function2` (c_date,user_id,function_key) VALUES (  '2014-03-13 10:37:34',2,null);
INSERT INTO `log_function2` (c_date,user_id,function_key) VALUES (  '2014-03-14 10:37:34',2,null);
INSERT INTO `log_function2` (c_date,user_id,function_key) VALUES (  '2014-03-15 10:37:34',2,null);
INSERT INTO `log_function2` (c_date,user_id,function_key) VALUES (  '2014-03-16 10:37:34',2,null);
INSERT INTO `log_function2` (c_date,user_id,function_key) VALUES (  '2014-03-17 10:37:34',2,null);
INSERT INTO `log_function2` (c_date,user_id,function_key) VALUES (  '2014-03-18 10:37:34',2,null);
                         
INSERT INTO `log_function2` (c_date,user_id,function_key) VALUES (  '2014-03-12 10:37:34',3,null);
INSERT INTO `log_function2` (c_date,user_id,function_key) VALUES (  '2014-03-13 10:37:34',3,null);
INSERT INTO `log_function2` (c_date,user_id,function_key) VALUES (  '2014-03-14 10:37:34',3,null);
INSERT INTO `log_function2` (c_date,user_id,function_key) VALUES (  '2014-03-15 10:37:34',3,null);
INSERT INTO `log_function2` (c_date,user_id,function_key) VALUES (  '2014-03-16 10:37:34',3,null);
INSERT INTO `log_function2` (c_date,user_id,function_key) VALUES (  '2014-03-17 10:37:34',3,null);
INSERT INTO `log_function2` (c_date,user_id,function_key) VALUES (  '2014-03-18 10:37:34',3,null);
                           
INSERT INTO `log_function2` (c_date,user_id,function_key) VALUES (  '2014-03-12 10:37:34',4,null);
INSERT INTO `log_function2` (c_date,user_id,function_key) VALUES (  '2014-03-13 10:37:34',4,null);
INSERT INTO `log_function2` (c_date,user_id,function_key) VALUES (  '2014-03-14 10:37:34',4,null);
INSERT INTO `log_function2` (c_date,user_id,function_key) VALUES (  '2014-03-15 10:37:34',4,null);
INSERT INTO `log_function2` (c_date,user_id,function_key) VALUES (  '2014-03-16 10:37:34',4,null);
INSERT INTO `log_function2` (c_date,user_id,function_key) VALUES (  '2014-03-17 10:37:34',4,null);
INSERT INTO `log_function2` (c_date,user_id,function_key) VALUES (  '2014-03-18 10:37:34',4,null);
                           
INSERT INTO `log_function2` (c_date,user_id,function_key) VALUES (  '2014-03-12 10:37:34',5,null);
INSERT INTO `log_function2` (c_date,user_id,function_key) VALUES (  '2014-03-13 10:37:34',5,null);
INSERT INTO `log_function2` (c_date,user_id,function_key) VALUES (  '2014-03-14 10:37:34',5,null);
INSERT INTO `log_function2` (c_date,user_id,function_key) VALUES (  '2014-03-15 10:37:34',5,null);
INSERT INTO `log_function2` (c_date,user_id,function_key) VALUES (  '2014-03-16 10:37:34',5,null);
INSERT INTO `log_function2` (c_date,user_id,function_key) VALUES (  '2014-03-17 10:37:34',5,null);
INSERT INTO `log_function2` (c_date,user_id,function_key) VALUES (  '2014-03-18 10:37:34',5,null);
                            
INSERT INTO `log_function2` (c_date,user_id,function_key) VALUES (  '2014-03-12 10:37:34',6,null);
INSERT INTO `log_function2` (c_date,user_id,function_key) VALUES (  '2014-03-13 10:37:34',6,null);
INSERT INTO `log_function2` (c_date,user_id,function_key) VALUES (  '2014-03-14 10:37:34',6,null);
INSERT INTO `log_function2` (c_date,user_id,function_key) VALUES (  '2014-03-15 10:37:34',6,null);
INSERT INTO `log_function2` (c_date,user_id,function_key) VALUES (  '2014-03-16 10:37:34',6,null);
INSERT INTO `log_function2` (c_date,user_id,function_key) VALUES (  '2014-03-17 10:37:34',6,null);
INSERT INTO `log_function2` (c_date,user_id,function_key) VALUES (  '2014-03-15 10:37:34',6,null);
                             
INSERT INTO `log_function2` (c_date,user_id,function_key) VALUES (  '2014-03-12 10:37:34',7,null);
INSERT INTO `log_function2` (c_date,user_id,function_key) VALUES (  '2014-03-13 10:37:34',7,null);
INSERT INTO `log_function2` (c_date,user_id,function_key) VALUES (  '2014-03-14 10:37:34',7,null);
INSERT INTO `log_function2` (c_date,user_id,function_key) VALUES (  '2014-03-15 10:37:34',7,null);
INSERT INTO `log_function2` (c_date,user_id,function_key) VALUES (  '2014-03-16 10:37:34',7,null);
INSERT INTO `log_function2` (c_date,user_id,function_key) VALUES (  '2014-03-17 10:37:34',7,null);
INSERT INTO `log_function2` (c_date,user_id,function_key) VALUES (  '2014-03-18 10:37:34',7,null);




加载中
返回顶部
顶部