修复会员沉睡天数数据.sql
1.41 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
-- 修复会员沉睡天数数据
-- 问题：耗卡记录的手工费为0时，LastConsumeTime未更新，导致沉睡天数计算错误
-- 解决方案：根据最新的有效耗卡记录更新LastConsumeTime
-- 1. 修复单个会员（陈秋竹）
UPDATE lq_khxx kh
INNER JOIN (
SELECT
hy,
MAX(hksj) as last_consume_time
FROM lq_xh_hyhk
WHERE hymc = '陈秋竹'
AND F_IsEffective = 1
AND xfje > 0
GROUP BY hy
) consume ON kh.F_Id = consume.hy
SET
kh.F_LastConsumeTime = consume.last_consume_time,
kh.F_SleepDays = GREATEST(0, DATEDIFF(CURDATE(), consume.last_consume_time))
WHERE kh.khmc = '陈秋竹'
AND kh.F_IsEffective = 1;
-- 2. 批量修复所有会员（根据最新的有效耗卡记录更新LastConsumeTime）
-- 注意：执行此SQL前请先备份数据库
UPDATE lq_khxx kh
INNER JOIN (
SELECT
hy,
MAX(hksj) as last_consume_time
FROM lq_xh_hyhk
WHERE F_IsEffective = 1
AND xfje > 0
GROUP BY hy
) consume ON kh.F_Id = consume.hy
SET
kh.F_LastConsumeTime = consume.last_consume_time,
kh.F_SleepDays = GREATEST(0, DATEDIFF(CURDATE(), consume.last_consume_time))
WHERE kh.F_IsEffective = 1
AND (
-- 只更新需要修复的记录：LastConsumeTime为空或LastConsumeTime小于最新的耗卡时间
kh.F_LastConsumeTime IS NULL
OR kh.F_LastConsumeTime < consume.last_consume_time
);