举栗:
我们的用户积分出现重复数据,然后我们要把重复数据删除掉,重新计算用户总积分,假设所有积分都是增加的积分。
用户积分表test_user_points
用户iduser_id
用户积分总数point_count
积分记录表test_point_log
用户iduser_id
用户积分总数point
SQL:
UPDATE test_user_points AS a
LEFT JOIN (
SELECT
user_id,
sum(point) AS point_count
FROM
test_point_log
GROUP BY
user_id
) AS b ON a.user_id = b.user_id
SET a.point_count = b.point_count
如果积分记录含有加减积分
积分记录表test_point_log
积分类型behavior_type
,obtain
为增加 consume
为消耗
UPDATE sysuser_user_points_local_bak AS a
LEFT JOIN (
SELECT
user_id,
sum(
CASE
WHEN behavior_type = 'obtain' THEN
point
ELSE
0
END
) - sum(
CASE
WHEN behavior_type = 'consume' THEN
point
ELSE
0
END
) AS point_count
FROM
sysuser_user_pointlog_online_bak_new
GROUP BY
user_id
) AS b ON a.user_id = b.user_id
SET a.point_count = b.point_count