MySQL的update语句里可以使用join,这在用一个表的数据更新另一个表时很方便,看下面一个统计点击数的例子:
-
- create table daily_hit_counter
- (
- day date not null,
- slot tinyint unsigned not null,
- cnt int unsigned not null,
- primary key (day,slot)
- ) engine=innodb;
-
-
- insert into daily_hit_counter(day,slot,cnt) values (current_date,rand()*100,1)
- on duplicate key update cnt=cnt+1;
-
-
- update daily_hit_counter as c inner join (select day,sum(cnt) as cnt min(slot) as mslot from daily_hit_counter group by day) 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);
- delete from daily_hit_counter where slot>0 and cnt=0;