检查生美业绩统计差异.sql
5.97 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
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
-- ============================================
-- 检查生美业绩统计差异
-- ============================================
-- 问题：品项明细表统计生美数据是1869781.81，但日报天王团统计教育一部+教育二部合计是1876061.21，差异6279.40
--
-- 可能原因：
-- 1. 门店在lq_md_target表中有重复记录（同一月份多条记录）
-- 2. 统计范围不一致（时间范围或门店范围）
-- 3. 数据关联逻辑问题
-- ============================================
-- 1. 检查lq_md_target表中是否有重复记录（同一门店同一月份多条记录）
-- ============================================
SELECT
F_StoreId,
F_Month,
COUNT(*) as record_count,
GROUP_CONCAT(DISTINCT F_EducationDepartment) as education_depts
FROM lq_md_target
WHERE F_Month = DATE_FORMAT(NOW(), '%Y%m')
AND (F_EducationDepartment IS NOT NULL AND F_EducationDepartment != '')
GROUP BY F_StoreId, F_Month
HAVING COUNT(*) > 1;
-- ============================================
-- 2. 检查品项明细表统计生美业绩（所有门店，不限制部门归属）
-- ============================================
SELECT
'品项明细表统计' AS 统计来源,
COALESCE(SUM(pxmx.F_ActualPrice), 0) as 生美业绩总额
FROM lq_kd_pxmx pxmx
INNER JOIN lq_kd_kdjlb billing ON pxmx.glkdbh = billing.F_Id
INNER JOIN lq_xmzl item ON pxmx.px = item.F_Id
WHERE pxmx.F_IsEffective = 1
AND billing.F_IsEffective = 1
AND item.F_IsEffective = 1
AND item.qt2 = '生美'
AND billing.kdrq >= DATE_FORMAT(NOW(), '%Y-%m-01')
AND billing.kdrq < DATE_ADD(DATE_FORMAT(NOW(), '%Y-%m-01'), INTERVAL 1 MONTH);
-- ============================================
-- 3. 检查日报天王团统计生美业绩（只统计有部门归属的门店，按部门分组）
-- ============================================
SELECT
'日报天王团统计' AS 统计来源,
target.F_EducationDepartment as 部门ID,
COALESCE(SUM(pxmx.F_ActualPrice), 0) as 生美业绩
FROM lq_kd_pxmx pxmx
INNER JOIN lq_kd_kdjlb billing ON pxmx.glkdbh = billing.F_Id
INNER JOIN lq_xmzl item ON pxmx.px = item.F_Id
INNER JOIN lq_md_target target ON billing.djmd = target.F_StoreId
AND target.F_Month = DATE_FORMAT(NOW(), '%Y%m')
WHERE pxmx.F_IsEffective = 1
AND billing.F_IsEffective = 1
AND item.F_IsEffective = 1
AND item.qt2 = '生美'
AND billing.kdrq >= DATE_FORMAT(NOW(), '%Y-%m-01')
AND billing.kdrq < DATE_ADD(DATE_FORMAT(NOW(), '%Y-%m-01'), INTERVAL 1 MONTH)
AND target.F_EducationDepartment IS NOT NULL
AND target.F_EducationDepartment != ''
GROUP BY target.F_EducationDepartment;
-- ============================================
-- 4. 检查是否有生美品项的开单记录，但门店在lq_md_target表中没有设置F_EducationDepartment
-- ============================================
SELECT
'未归属门店的生美业绩' AS 统计来源,
COUNT(DISTINCT billing.djmd) as 门店数量,
COUNT(*) as 开单记录数,
COALESCE(SUM(pxmx.F_ActualPrice), 0) as 生美业绩总额
FROM lq_kd_pxmx pxmx
INNER JOIN lq_kd_kdjlb billing ON pxmx.glkdbh = billing.F_Id
INNER JOIN lq_xmzl item ON pxmx.px = item.F_Id
LEFT JOIN lq_md_target target ON billing.djmd = target.F_StoreId
AND target.F_Month = DATE_FORMAT(NOW(), '%Y%m')
WHERE pxmx.F_IsEffective = 1
AND billing.F_IsEffective = 1
AND item.F_IsEffective = 1
AND item.qt2 = '生美'
AND billing.kdrq >= DATE_FORMAT(NOW(), '%Y-%m-01')
AND billing.kdrq < DATE_ADD(DATE_FORMAT(NOW(), '%Y-%m-01'), INTERVAL 1 MONTH)
AND (target.F_StoreId IS NULL
OR target.F_EducationDepartment IS NULL
OR target.F_EducationDepartment = '');
-- ============================================
-- 5. 检查是否有门店在lq_md_target表中有多条记录（可能导致重复统计）
-- ============================================
SELECT
billing.djmd as 门店ID,
COUNT(DISTINCT target.F_Id) as 目标表记录数,
COUNT(*) as 开单记录数,
COALESCE(SUM(pxmx.F_ActualPrice), 0) as 生美业绩总额,
GROUP_CONCAT(DISTINCT target.F_EducationDepartment) as 教育部门列表
FROM lq_kd_pxmx pxmx
INNER JOIN lq_kd_kdjlb billing ON pxmx.glkdbh = billing.F_Id
INNER JOIN lq_xmzl item ON pxmx.px = item.F_Id
INNER JOIN lq_md_target target ON billing.djmd = target.F_StoreId
AND target.F_Month = DATE_FORMAT(NOW(), '%Y%m')
WHERE pxmx.F_IsEffective = 1
AND billing.F_IsEffective = 1
AND item.F_IsEffective = 1
AND item.qt2 = '生美'
AND billing.kdrq >= DATE_FORMAT(NOW(), '%Y-%m-01')
AND billing.kdrq < DATE_ADD(DATE_FORMAT(NOW(), '%Y-%m-01'), INTERVAL 1 MONTH)
AND target.F_EducationDepartment IS NOT NULL
AND target.F_EducationDepartment != ''
GROUP BY billing.djmd
HAVING COUNT(DISTINCT target.F_Id) > 1;
-- ============================================
-- 6. 详细检查：查看每个门店的生美业绩和部门归属情况
-- ============================================
SELECT
billing.djmd as 门店ID,
md.Dm as 门店名称,
target.F_EducationDepartment as 教育部门ID,
dept.F_FullName as 教育部门名称,
COUNT(*) as 开单记录数,
COALESCE(SUM(pxmx.F_ActualPrice), 0) as 生美业绩
FROM lq_kd_pxmx pxmx
INNER JOIN lq_kd_kdjlb billing ON pxmx.glkdbh = billing.F_Id
INNER JOIN lq_xmzl item ON pxmx.px = item.F_Id
INNER JOIN lq_md_target target ON billing.djmd = target.F_StoreId
AND target.F_Month = DATE_FORMAT(NOW(), '%Y%m')
LEFT JOIN lq_mdxx md ON billing.djmd = md.F_Id
LEFT JOIN base_organize dept ON target.F_EducationDepartment = dept.F_Id
WHERE pxmx.F_IsEffective = 1
AND billing.F_IsEffective = 1
AND item.F_IsEffective = 1
AND item.qt2 = '生美'
AND billing.kdrq >= DATE_FORMAT(NOW(), '%Y-%m-01')
AND billing.kdrq < DATE_ADD(DATE_FORMAT(NOW(), '%Y-%m-01'), INTERVAL 1 MONTH)
AND target.F_EducationDepartment IS NOT NULL
AND target.F_EducationDepartment != ''
GROUP BY billing.djmd, md.Dm, target.F_EducationDepartment, dept.F_FullName
ORDER BY 生美业绩 DESC;