排查生美业绩统计差异-简化版.sql
5.04 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
-- ============================================
-- 排查生美业绩统计差异 - 简化版
-- ============================================
-- 问题：品项明细表统计生美数据是1869781.81，但日报天王团统计教育一部+教育二部合计是1876061.21，差异6279.40
--
-- 分析思路：
-- 1. 检查是否有门店在lq_md_target表中有多条记录（同一月份）
-- 2. 对比品项明细表统计和日报天王团统计的差异
-- 3. 检查是否有数据被重复统计
-- ============================================
-- 1. 品项明细表统计生美业绩（所有门店，不限制部门归属）
-- ============================================
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);
-- ============================================
-- 2. 日报天王团统计生美业绩（只统计有部门归属的门店，按部门分组）
-- ============================================
SELECT
'日报天王团统计' AS 统计来源,
target.F_EducationDepartment as 部门ID,
dept.F_FullName 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 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 target.F_EducationDepartment, dept.F_FullName;
-- ============================================
-- 3. 检查是否有门店在lq_md_target表中有多条记录（同一月份）
-- ============================================
SELECT
F_StoreId,
F_Month,
COUNT(*) as record_count
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;
-- ============================================
-- 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. 关键检查：查看每个门店的生美业绩，看看是否有重复统计
-- ============================================
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 生美业绩,
COUNT(DISTINCT target.F_Id) 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
HAVING COUNT(DISTINCT target.F_Id) > 1
ORDER BY 生美业绩 DESC;