SELECT * FROM `equipment_operate` a WHERE NOT EXISTS (SELECT 1 FROM `equipment_operate` WHERE a.`uid`=`uid` AND a.`OperateDate`<`OperateDate`)
谢谢您上面的方法 我现在获取到了这些数据 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.equipmentidUID equipmentid getjf 21 3 30 20 4 50我update points_summary 表的时候该如何做呢,条件那里
update points_summary a inner join (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) c on a.uid=c.uid set a.Points=a.Points+c.getjf
set a.points=b.points+5
假设表中有唯一标识的字段ID
select a.* from tt a where not exists(select 1 from tt where a.f1=f1 and a.id<id)
update summary a inner join getpoints b on a.uid=b.uid
set a.points=b.points+5
我详细点,谢谢回答,
有getpoints_info,getpoints_operate,summarygetpoints_infogetpointsid,name ,points
1 无装备 10
2 普通装备 20
3 强化装备 50
getpoints_operateoperateid,uid,getpointsid,createdate
1 1 3
2 2 1
summaryid,uid,points
1 1 50
2 2 10
上面为三个表必要字段,一些没用就没列出来了,现在就是想每过一个小时对summary表进行更新,每天增加8小时,这样子,八小时那里可以先不考虑。如果不清楚可以继续阐明
对的,使用每条数据的各自最后一项,
因为getpoints_operate 的装备是变化的,所以是获取最新的一项即最后的一项进行从getpoints_info那里获取相应的points
参考一下这个贴子的提问方式http://topic.csdn.net/u/20091130/20/8343ee6a-417c-4c2d-9415-fa46604a00cf.html
1. 你的 create table xxx .. 语句
2. 你的 insert into xxx ... 语句
3. 结果是什么样,(并给以简单的算法描述)
4. 你用的数据库名称和版本(经常有人在MS SQL server版问 MySQL)
这样想帮你的人可以直接搭建和你相同的环境,并在给出方案前进行测试,避免文字描述理解上的误差。
equipment_info/*
Navicat MySQL Data TransferSource Server : suibao
Source Server Version : 50403
Source Host : 58.63.245.183:3306
Source Database : k3gelTarget Server Type : MYSQL
Target Server Version : 50403
File Encoding : 65001Date: 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 TransferSource Server : suibao
Source Server Version : 50403
Source Host : 58.63.245.183:3306
Source Database : k3gelTarget Server Type : MYSQL
Target Server Version : 50403
File Encoding : 65001Date: 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 TransferSource Server : suibao
Source Server Version : 50403
Source Host : 58.63.245.183:3306
Source Database : k3gelTarget Server Type : MYSQL
Target Server Version : 50403
File Encoding : 65001Date: 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,依此类推
20 21 3 75 2012-12-10 11:45:26这样子
还是
`EquipmentID`=1、3、4的记录?
WHERE NOT EXISTS
(SELECT 1 FROM `equipment_operate` WHERE a.`uid`=`uid` AND a.`OperateDate`<`OperateDate`)
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.equipmentidUID equipmentid getjf
21 3 30
20 4 50我update points_summary 表的时候该如何做呢,条件那里
(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) c
on a.uid=c.uid
set a.Points=a.Points+c.getjf