include('conn.php');
include('db.php');
$dbhelper=new DBHelper();
$connection=$dbhelper->GetConnection($db_host,$db_username,$db_password);
mysql_query("SET NAMES 'gbk'");
//调用建立方法
$dbhelper->DBSelect('test1');//选择数据库
$sqlstring="select * from table1 ";
$result=$dbhelper->Excute($sqlstring);while($row=mysql_fetch_array($result)){
$sql1="SELECT sum(取款金额) AS totole FROM table1 WHERE ATM号=$row[3] AND 交易日期=$row[4]";
$result1=$dbhelper->Excute($sql1);
while($row1=mysql_fetch_array($result1)){
$sql2="update table1 set 日交易=$row1[0] where id=$row[0]";
$result2=$dbhelper->Excute($sql2);
}
}
$dbhelper->CloseConnection($connection)我想问下,大概10万条数据,处理到大概5000条就执行不动了,在进程里还耗用大量的cpu,但页面已经死了,404错误,inodb,mysql5.1.5,请问,这种情况遇到过没?或有什么解决、优化的办法,
你是想,对表table1中的某列"日交易"生成值,其值按交易日期和ATM号进行分组求和,并把这个值存储到每一行的id上,id就是该表的主键吧,是吗?这个大概用一句话就可以表达清楚的。
create table t_atm_update(id int primary key, sum_trans_date INT, t datetime, atm_id INT, col5 INT);update (select t, atm_id, sum(sum_trans_date) s from t_atm_update group by t, atm_id) t2, t_atm_update t1 set t1.col5 = t2.s where t1.t = t2.t and t1.atm_id = t2.atm_id;
update table1 set 日交易=$row1[0] where id=$row[0];估计你的Id是主键,则要优化后面这个
SELECT sum(取款金额) AS totole FROM table1 WHERE ATM号=$row[3] AND 交易日期=$row[4]
建立索引:(ATM号,交易日期,交易日期)但对与table1 你怎么设计的