MySQL里用一个表的数据更新另一个表

版权声明：本文为博主原创文章，未经博主允许不得转载。 https://cloud.tencent.com/developer/article/1433218

MySQL的update语句里可以使用join，这在用一个表的数据更新另一个表时很方便，看下面一个统计点击数的例子：

sql view plain copy

1. -- 建立每天点击统计表
2. create table daily_hit_counter
3. (
4. day date not null,
5. slot tinyint unsigned not null,
6. cnt int unsigned not null,
7. primary key (day,slot)
8. ) engine=innodb;
10. -- 每次点击更新点击数
11. insert into daily_hit_counter(day,slot,cnt) values (current_date,rand()*100,1)
12. on duplicate key update cnt=cnt+1;
14. -- 按天合并统计数，并删除多余行
15. 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
16. using(day) set c.cnt=if(c.slot=x.mslot,x.cnt,0), c.slot=if(c.slot=x.mslot,0,c.slot);
17. delete from daily_hit_counter where slot>0 and cnt=0;