mysql_query("SET AUTOCOMMIT=0");//设置为不自动提交,原因是MYSQL默认立即执行
mysql_query("BEGIN");//开始事务定义
$sql_m="INSERT INTO mar_sum(sum_uid,sum_fen,sum_type,sum_time) SELECT nl_uid,SUM(nl_fen) as q,nl_ew,DATE_FORMAT(nl_time,"%Y-%m-%d") AS OrderDay FROM mar_user_log where nl_uid=".$Cookies0." and nl_ew=3 and date_format(nl_time,"%Y-%m-%d")<curdate() GROUP by OrderDay ";
if(!mysql_query($sql_m)){
mysql_query("rollback");//判断当执行失败时回滚
exit("打不开?请在公共平台截图你的问题,");
}
$sql_n="Delete FROM mar_user_log WHERE nl_uid=".$Cookies0." and nl_ew=3 and date_format(nl_time,"%Y-%m-%d")<curdate() ";
if(!mysql_query($sql_n)){
mysql_query("rollback");//判断当执行失败时回滚
}
mysql_query("commit") or exit(mysql_error());//执行事务
mysql_query("SET AUTOCOMMIT=1");
}
$ip_sql="select id from mar_user ";
$res9=_query($ip_sql);
$i=0;
while (!!$rs1=_fetch_array($res9)){
$Cookies0 = $rs1["id"];
$sql_m="INSERT INTO mar_sum(sum_uid,sum_fen,sum_type,sum_time) SELECT nl_uid,SUM(nl_fen) as q,nl_ew,DATE_FORMAT(nl_time,"%Y-%m-%d") AS OrderDay FROM mar_user_log where nl_uid=".$Cookies0." and nl_ew=3 and date_format(nl_time,"%Y-%m-%d")<curdate() GROUP by OrderDay ";
_query($sql_m);
$sql_n="Delete FROM mar_user_log WHERE nl_uid=".$Cookies0." and nl_ew=3 and date_format(nl_time,"%Y-%m-%d")<curdate() ";
_query($sql_n);
}
就是你的意思…
但是怎么加进去啊…他是按会员的ID 跟时间汇总的….
$sql_m=”INSERT INTO mar_sum(sum_uid,sum_fen,sum_type,sum_time) SELECT nl_uid,SUM(nl_fen) as q,nl_ew,DATE_FORMAT(nl_time,”%Y-%m-%d”) AS OrderDay FROM mar_user_log where nl_uid=”.$Cookies0.” and nl_ew=3 and date_format(nl_time,”%Y-%m-%d”)<curdate() GROUP by OrderDay “;
下面这个nl_uid有30万.. 不循环 怎么实现汇总….循环的话 limit最多只能达到5000 超过就卡死了
where nl_uid=”.$Cookies0.”
备注mar_user表(会员信息)$Cookies0为会员ID (数据30万)
备注mar_sum表(统计会员每天的点击数)
备注mar_user_log表(会员点击一次,写入数据库一次,数据700W)
本人觉得能否可以这样,一开始就有30万用户,然后汇总之前的数据到一个临时表,然后通过update语句直接更新 汇总表的记录,例如:
update 汇总表 a, 临时表 b set a.xx= a.xx+b.xx where a.userid=b.userid