CREATE TABLE comment_record (ID int(64) NOT NULL AUTO_INCREMENT COMMENT 'ID',COMMENTED_ID varchar(64) DEFAULT NULL COMMENT '被评论人ID',COMMENTED_ROLE varchar(20) DEFAULT NULL COMMENT '被评论人角色',INDICATOR_NAME varchar(128) DEFAULT NULL COMMENT '指标名称',POSITIVE_RATE varchar(20) DEFAULT NULL COMMENT '好评率',YEAR_QUARTER varchar(20) DEFAULT NULL COMMENT '年-季',REMARKS varchar(255) DEFAULT NULL COMMENT '备注信息',CREATE_BY varchar(64) DEFAULT NULL COMMENT '创建者',CREATE_DATE varchar(64) DEFAULT NULL COMMENT '创建时间',UPDATE_BY varchar(64) DEFAULT NULL COMMENT '更新者',UPDATE_DATE varchar(64) DEFAULT NULL COMMENT '更新时间',DEL_FLAG char(1) NOT NULL COMMENT '删除标记',COMMENT_GRADEA_NUM int(50) DEFAULT NULL COMMENT '优的数量',COMMENT_GRADEB_NUM int(50) DEFAULT NULL COMMENT '良的数量',COMMENT_GRADEC_NUM int(50) DEFAULT NULL COMMENT '差的数量',INDICATOR_TYPE varchar(128) DEFAULT NULL COMMENT '指标类型',DIFFPOSITIVE_RATE varchar(128) DEFAULT NULL COMMENT '季度差',
PRIMARY KEY (ID),
KEY IDX_HONESTY_COMMENT_RECORD_01 (COMMENTED_ID)
) ENGINE=InnoDB AUTO_INCREMENT=725 DEFAULT CHARSET=utf8 COMMENT='用户评论统计表'
把每条数据的这个季度的好评率减去上个季度的好评率得到的值更新到DIFFPOSITIVE_RATE中
测试数据
insert into t_honesty_comment_record(ID,COMMENTED_ID,COMMENTED_ROLE,INDICATOR_NAME,POSITIVE_RATE,YEAR_QUARTER,REMARKS,CREATE_BY,CREATE_DATE,UPDATE_BY,UPDATE_DATE,DEL_FLAG,COMMENT_GRADEA_NUM,COMMENT_GRADEB_NUM,COMMENT_GRADEC_NUM,INDICATOR_TYPE,DIFFPOSITIVE_RATE) values (710,'1','10','货源真实性','1.000000000','2017_2',NULL,NULL,NULL,NULL,NULL,'0',2,0,0,'2010','0'),(711,'1','10','装卸货及时性','0','2017_2',NULL,NULL,NULL,NULL,NULL,'0',0,2,0,'2020','0'),(712,'1','20','运输服务规范','0.666666666','2017_2',NULL,NULL,NULL,NULL,NULL,'0',2,1,0,'1010','0'),(713,'1','20','物流发货速度','0.333333333','2017_2',NULL,NULL,NULL,NULL,NULL,'0',1,1,1,'1020','0'),(714,'1000006880','20','运输服务规范','1.000000000','2017_2',NULL,NULL,NULL,NULL,NULL,'0',2,0,0,'1010','0'),(715,'1000006880','20','物流发货速度','0','2017_2',NULL,NULL,NULL,NULL,NULL,'0',0,0,2,'1020','0'),(716,'1000006889','10','货源真实性','0','2017_2',NULL,NULL,NULL,NULL,NULL,'0',0,0,1,'2010','0'),(717,'1000006889','10','装卸货及时性','0','2017_2',NULL,NULL,NULL,NULL,NULL,'0',0,1,0,'2020','0'),(718,'1000006912','20','运输服务规范','0','2017_2',NULL,NULL,NULL,NULL,NULL,'0',0,0,1,'1010','0'),(719,'1000006912','20','物流发货速度','0','2017_2',NULL,NULL,NULL,NULL,NULL,'0',0,1,0,'1020','0'),(720,'9100000095','10','货源真实性','1.000000000','2017_2',NULL,NULL,NULL,NULL,NULL,'0',2,0,0,'2010','0'),(721,'9100000095','10','装卸货及时性','0.500000000','2017_2',NULL,NULL,NULL,NULL,NULL,'0',1,1,0,'2020','0'),(722,'9100000107','10','货源真实性','1.000000000','2017_2',NULL,NULL,NULL,NULL,NULL,'0',2,0,0,'2010','0'),(723,'9100000107','10','装卸货及时性','0','2017_2',NULL,NULL,NULL,NULL,NULL,'0',0,2,0,'2020','0');