37 lines
1.2 KiB
SQL
37 lines
1.2 KiB
SQL
-- 修复积分统计数据
|
|
-- 根据 points_record 表的记录,重新计算每个用户的 totalEarned 和 totalSpent
|
|
|
|
-- 1. 更新 totalEarned (累计获得) - 统计所有 type='earn' 的记录
|
|
UPDATE user_points up
|
|
SET totalEarned = (
|
|
SELECT COALESCE(SUM(points), 0)
|
|
FROM points_record pr
|
|
WHERE pr.user_id = up.user_id
|
|
AND pr.type = 'earn'
|
|
);
|
|
|
|
-- 2. 更新 totalSpent (累计消费) - 统计所有 type='consume' 或 type='spend' 的记录
|
|
UPDATE user_points up
|
|
SET totalSpent = (
|
|
SELECT COALESCE(SUM(ABS(points)), 0)
|
|
FROM points_record pr
|
|
WHERE pr.user_id = up.user_id
|
|
AND pr.type IN ('consume', 'spend')
|
|
);
|
|
|
|
-- 3. 更新 balance (当前余额) - totalEarned - totalSpent
|
|
UPDATE user_points
|
|
SET balance = totalEarned - totalSpent;
|
|
|
|
-- 4. 验证修复结果
|
|
SELECT
|
|
up.id,
|
|
up.user_id,
|
|
up.balance as '当前余额',
|
|
up.totalEarned as '累计获得',
|
|
up.totalSpent as '累计消费',
|
|
(SELECT COUNT(*) FROM points_record WHERE user_id = up.user_id AND type = 'earn') as '获得记录数',
|
|
(SELECT COUNT(*) FROM points_record WHERE user_id = up.user_id AND type IN ('consume', 'spend')) as '消费记录数'
|
|
FROM user_points up
|
|
ORDER BY up.id;
|