56,677
社区成员
发帖
与我相关
我的任务
分享
select eo.uid,eo.equipmentid,ei.getjf from equipment_operate eo ,equipment_info ei
where not exists(select 1 from equipment_operate where eo.uid=uid
and eo.operateid<operateid) and eo.equipmentid=ei.equipmentid
UID equipmentid getjf
21 3 30
20 4 50
我update points_summary 表的时候该如何做呢,条件那里
INSERT INTO `points_summary` VALUES ('21', '22', '3', '10', '2012-12-07 11:48:40');
可以不要,谢谢
SummaryID UID PromotionID Points Update_date
19 20 3 25 2012-12-07 11:00:12
20 21 3 75 2012-12-10 11:45:26
这样子
/*
Navicat MySQL Data Transfer
Source Server : suibao
Source Server Version : 50403
Source Host : 58.63.245.183:3306
Source Database : k3gel
Target Server Type : MYSQL
Target Server Version : 50403
File Encoding : 65001
Date: 2012-12-10 18:29:51
*/
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for `equipment_info`
-- ----------------------------
DROP TABLE IF EXISTS `equipment_info`;
CREATE TABLE `equipment_info` (
`EquipmentID` int(11) NOT NULL AUTO_INCREMENT,
`E_Name` varchar(50) DEFAULT NULL,
`NeedJF` int(11) DEFAULT NULL,
`SleepIndex` int(11) DEFAULT NULL,
`GetJF` int(11) DEFAULT NULL,
`Memo` varchar(256) DEFAULT NULL,
PRIMARY KEY (`EquipmentID`),
UNIQUE KEY `EquipmentID_UNIQUE` (`EquipmentID`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of equipment_info
-- ----------------------------
INSERT INTO `equipment_info` VALUES ('1', '无', '0', '1', '10', null);
INSERT INTO `equipment_info` VALUES ('2', '普通', '20', '2', '20', null);
INSERT INTO `equipment_info` VALUES ('3', '强化', '50', '3', '30', null);
INSERT INTO `equipment_info` VALUES ('4', '究极', '150', '5', '50', null);
equipment_operate
/*
Navicat MySQL Data Transfer
Source Server : suibao
Source Server Version : 50403
Source Host : 58.63.245.183:3306
Source Database : k3gel
Target Server Type : MYSQL
Target Server Version : 50403
File Encoding : 65001
Date: 2012-12-10 18:30:03
*/
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for `equipment_operate`
-- ----------------------------
DROP TABLE IF EXISTS `equipment_operate`;
CREATE TABLE `equipment_operate` (
`OperateID` int(11) NOT NULL AUTO_INCREMENT,
`UID` int(11) DEFAULT NULL,
`EquipmentID` int(11) DEFAULT NULL,
`OperateDate` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
`memo` varchar(50) DEFAULT NULL,
`AdminID` int(11) DEFAULT NULL,
PRIMARY KEY (`OperateID`),
UNIQUE KEY `OperateID_UNIQUE` (`OperateID`)
) ENGINE=InnoDB AUTO_INCREMENT=82 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of equipment_operate
-- ----------------------------
INSERT INTO `equipment_operate` VALUES ('15', '20', '1', '2012-12-05 18:51:52', 'initEquip', '1');
INSERT INTO `equipment_operate` VALUES ('16', '21', '1', '2012-12-05 18:53:08', 'initEquip', '1');
INSERT INTO `equipment_operate` VALUES ('17', '21', '3', '2012-12-06 18:03:15', 'GetEquip', '1');
INSERT INTO `equipment_operate` VALUES ('18', '20', '4', '2012-12-06 23:14:44', 'GetEquip', '1');
points_summary
/*
Navicat MySQL Data Transfer
Source Server : suibao
Source Server Version : 50403
Source Host : 58.63.245.183:3306
Source Database : k3gel
Target Server Type : MYSQL
Target Server Version : 50403
File Encoding : 65001
Date: 2012-12-10 18:30:16
*/
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for `points_summary`
-- ----------------------------
DROP TABLE IF EXISTS `points_summary`;
CREATE TABLE `points_summary` (
`SummaryID` int(11) NOT NULL AUTO_INCREMENT,
`UID` int(11) DEFAULT NULL,
`PromotionID` int(11) DEFAULT NULL,
`Points` int(11) DEFAULT NULL,
`Update_date` datetime DEFAULT NULL,
PRIMARY KEY (`SummaryID`)
) ENGINE=InnoDB AUTO_INCREMENT=81 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of points_summary
-- ----------------------------
INSERT INTO `points_summary` VALUES ('19', '20', '3', '15', '2012-12-07 11:00:12');
INSERT INTO `points_summary` VALUES ('20', '21', '3', '25', '2012-12-10 11:45:26');
INSERT INTO `points_summary` VALUES ('21', '22', '3', '10', '2012-12-07 11:48:40');
就是 现在就是想每过一个小时对summary表的points进行更新,每天增加8小时,比如uid为1 的用户现在 getpointsid 是3 (这里要先从getpoints_operate 获取当前uid最后的那条数据,现在此用户的最后一条记录的getpointsid为3),所以每个小时就要增加50点,summary的points=points+50,依此类推