19 lines
771 B
SQL
Executable File
19 lines
771 B
SQL
Executable File
-- 1. 修复【金额券】：余额已为 0，且尚未到期，但状态被错误标记为"已过期”(3) 的券，统一修复为"已使用”(2)
|
||
UPDATE user_coupons
|
||
SET status = 2,
|
||
used_at = IFNULL(used_at, updated_at)
|
||
WHERE balance_amount = 0
|
||
AND status = 3
|
||
AND valid_end > NOW();
|
||
|
||
-- 2. 修复【核销记录一致性】：已经在 order_coupons 表中有抵扣记录，但状态仍为"已过期”(3) 的券
|
||
UPDATE user_coupons uc
|
||
JOIN order_coupons oc ON uc.id = oc.user_coupon_id
|
||
SET uc.status = 2,
|
||
uc.used_at = IFNULL(uc.used_at, oc.created_at)
|
||
WHERE uc.status = 3
|
||
AND uc.valid_end > NOW();
|
||
|
||
-- 3. (可选) 检查修复后的 User 9090 数据
|
||
-- SELECT id, status, balance_amount, valid_end, used_at FROM user_coupons WHERE user_id = 9090;
|