修复人次记录HasBilling字段.sql
1.27 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
45
46
47
-- 修复人次记录表中F_HasBilling字段
-- 对于耗卡类型的人次记录，如果会员有开单记录（F_IsEffective=1且Sfyj>0），则设置F_HasBilling=1
-- 1. 先查看需要修复的记录数量
SELECT COUNT(*) as need_fix_count
FROM lq_person_times_record ptr
WHERE ptr.F_BusinessType = '耗卡'
AND ptr.F_HasBilling = 0
AND ptr.F_MemberId IS NOT NULL
AND ptr.F_MemberId != ''
AND EXISTS (
SELECT 1
FROM lq_kd_kdjlb kd
WHERE kd.Kdhy = ptr.F_MemberId
AND kd.F_IsEffective = 1
AND kd.Sfyj > 0
);
-- 2. 修复耗卡类型的人次记录
UPDATE lq_person_times_record ptr
INNER JOIN (
SELECT DISTINCT Kdhy
FROM lq_kd_kdjlb
WHERE F_IsEffective = 1
AND Sfyj > 0
) kd ON ptr.F_MemberId = kd.Kdhy
SET ptr.F_HasBilling = 1
WHERE ptr.F_BusinessType = '耗卡'
AND ptr.F_HasBilling = 0
AND ptr.F_MemberId IS NOT NULL
AND ptr.F_MemberId != '';
-- 3. 验证修复结果（可选）
SELECT COUNT(*) as fixed_count
FROM lq_person_times_record ptr
WHERE ptr.F_BusinessType = '耗卡'
AND ptr.F_HasBilling = 1
AND ptr.F_MemberId IS NOT NULL
AND ptr.F_MemberId != ''
AND EXISTS (
SELECT 1
FROM lq_kd_kdjlb kd
WHERE kd.Kdhy = ptr.F_MemberId
AND kd.F_IsEffective = 1
AND kd.Sfyj > 0
);