MySQL的update语句里可以使用join,这在用一个表的数据更新另一个表时很方便,看下面一个统计点击数的例子:
-- 建立每天点击统计表
createtable daily_hit_counter
(
daydatenotnull,
slot tinyint unsigned notnull,
cnt int unsigned notnull,
primarykey (day,slot)
) engine=innodb;
-- 每次点击更新点击数
insertinto daily_hit_counter(day,slot,cnt) values (current_date,rand()*100,1)
on duplicate keyupdate cnt=cnt+1;
-- 按天合并统计数,并删除多余行
update daily_hit_counter as c innerjoin (selectday,sum(cnt) as cnt min(slot) as mslot from daily_hit_counter groupbyday) as x
using(day) set c.cnt=if(c.slot=x.mslot,x.cnt,0), c.slot=if(c.slot=x.mslot,0,c.slot);
deletefrom daily_hit_counter where slot>0 and cnt=0;
--转自