项目中,评论数,关注数等数据,是实时更新的。+1,-1 这种。
有的时候,可能统计不准确。
需要写一个统计工具,更新校准下。
用Java写SQL和函数,代码很清晰,方便扩展,但是太慢了。
为了简单起见,只写sql来统计,然后更新。(不想写存储过程)
语句如下:
#更新一个人的 关注数 followingCount
- update behavior_redman_count a
- inner join
- (
- select memberId,count(*) as followingCount from behavior_follow where type = 10
- and isDelete=0 group by memberId
- )b
- set a.followingCount =b.followingCount
- where a.redmanId = b.memberId;
update behavior_redman_count a
inner join
(
select memberId,count(*) as followingCount from behavior_follow where type = 10
and isDelete=0 group by memberId
)b
set a.followingCount =b.followingCount
where a.redmanId = b.memberId;
#MySQL不支持这种语法
- update behavior_redman_count
- set followingCount = b.followingCount
- from
- (
- select count(*) as followingCount from behavior_follow where type = 10 and memberId = 198
- and isDelete=0
- )b
- where redmandId = a.memberId and a.memberId= 198;
update behavior_redman_count
set followingCount = b.followingCount
from
(
select count(*) as followingCount from behavior_follow where type = 10 and memberId = 198
and isDelete=0
)b
where redmandId = a.memberId and a.memberId= 198;
参考资料
1. Mysql跨表更新 多表update sql语句总结
http://www.jb51.net/article/32648.htm
2.如何将select出来的部分数据update到另一个表里面?
http://bbs.csdn.net/topics/320233666