排查生美业绩统计差异-简化版.sql
5.03 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
-- ============================================
-- 排查生美业绩统计差异 - 简化版
-- ============================================
-- 问题：品项明细表统计生美数据是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;