科美业绩与科技部老师开单业绩差异核对.sql
4.57 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
-- ============================================================
-- 科美业绩(1163803) vs 科技部老师开单业绩(1165702.63) 差异核对
-- 理论上两者应相等，差异约 1899.63。本脚本用于排查原因，不修改任何数据。
-- ============================================================
-- 1) 口径说明
-- 科美业绩：一般指「开单品项明细」中 品项分类=科美 的 F_ActualPrice 之和（或来自台账选科美时的汇总）
-- 科技部老师开单业绩：lq_kd_kjbsyj 表的 kjblsyj 之和
-- 2) 同一时间范围下两数分别多少（请按需修改时间）
SET @start = '2026-01-01 00:00:00';
SET @end = '2026-01-31 23:59:59';
-- 2.1 科美业绩：按开单品项明细，品项分类=科美，有效开单+有效明细
SELECT
'科美业绩-按品项明细(pxmx.F_ItemCategory=科美)' AS 口径,
COALESCE(SUM(px.F_ActualPrice), 0) AS 金额
FROM lq_kd_pxmx px
INNER JOIN lq_kd_kdjlb kd ON px.glkdbh = kd.F_Id AND kd.F_IsEffective = 1
WHERE px.F_IsEffective = 1
AND (px.F_ItemCategory = '科美' OR EXISTS (SELECT 1 FROM lq_xmzl zl WHERE zl.F_Id = px.px AND zl.F_IsEffective = 1 AND zl.qt2 = '科美'))
AND kd.kdrq >= @start
AND kd.kdrq <= @end;
-- 2.2 科技部老师开单业绩：lq_kd_kjbsyj 全表汇总（当前系统统计方式，未过滤科美）
SELECT
'科技部老师开单业绩-全表kjblsyj' AS 口径,
COALESCE(SUM(CAST(NULLIF(TRIM(k.kjblsyj), '') AS DECIMAL(18,2))), 0) AS 金额
FROM lq_kd_kjbsyj k
INNER JOIN lq_kd_kdjlb kd ON k.glkdbh = kd.F_Id AND kd.F_IsEffective = 1
WHERE k.F_IsEffective = 1
AND k.yjsj >= @start
AND k.yjsj <= @end
AND k.kjblsyj IS NOT NULL
AND TRIM(k.kjblsyj) != ''
AND TRIM(k.kjblsyj) != '0';
-- 2.3 科技部老师开单业绩：仅 F_ItemCategory='科美'（若只统计科美，应与科美业绩一致）
SELECT
'科技部老师开单业绩-仅科美F_ItemCategory' AS 口径,
COALESCE(SUM(CAST(NULLIF(TRIM(k.kjblsyj), '') AS DECIMAL(18,2))), 0) AS 金额
FROM lq_kd_kjbsyj k
INNER JOIN lq_kd_kdjlb kd ON k.glkdbh = kd.F_Id AND kd.F_IsEffective = 1
WHERE k.F_IsEffective = 1
AND k.F_ItemCategory = '科美'
AND k.yjsj >= @start
AND k.yjsj <= @end
AND k.kjblsyj IS NOT NULL
AND TRIM(k.kjblsyj) != ''
AND TRIM(k.kjblsyj) != '0';
-- 3) 差异来源：科技部表里「非科美」或「品项分类为空」的金额（多出来的部分）
SELECT
'科技部-非科美或空分类金额' AS 口径,
COALESCE(SUM(CAST(NULLIF(TRIM(k.kjblsyj), '') AS DECIMAL(18,2))), 0) AS 金额
FROM lq_kd_kjbsyj k
INNER JOIN lq_kd_kdjlb kd ON k.glkdbh = kd.F_Id AND kd.F_IsEffective = 1
WHERE k.F_IsEffective = 1
AND k.yjsj >= @start
AND k.yjsj <= @end
AND (k.F_ItemCategory IS NULL OR k.F_ItemCategory = '' OR k.F_ItemCategory != '科美')
AND k.kjblsyj IS NOT NULL
AND TRIM(k.kjblsyj) != ''
AND TRIM(k.kjblsyj) != '0';
-- 4) 科美品项有明细但科技部无业绩 / 科技部有业绩但品项非科美（明细级抽查）
-- 4.1 科美品项明细对应的科技部业绩合计（按开单明细ID关联）
SELECT
'按开单明细关联-科美品项对应kjbsyj之和' AS 口径,
COALESCE(SUM(CAST(NULLIF(TRIM(k.kjblsyj), '') AS DECIMAL(18,2))), 0) AS 金额
FROM lq_kd_pxmx px
INNER JOIN lq_kd_kdjlb kd ON px.glkdbh = kd.F_Id AND kd.F_IsEffective = 1
LEFT JOIN lq_kd_kjbsyj k ON k.F_kdpxid = px.F_Id AND k.F_IsEffective = 1
WHERE px.F_IsEffective = 1
AND px.F_ItemCategory = '科美'
AND kd.kdrq >= @start
AND kd.kdrq <= @end
AND k.kjblsyj IS NOT NULL
AND TRIM(k.kjblsyj) != ''
AND TRIM(k.kjblsyj) != '0';
-- 4.2 科美品项明细的 ActualPrice 之和（与 4.1 同范围，用于对比）
SELECT
'科美品项ActualPrice之和(同范围)' AS 口径,
COALESCE(SUM(px.F_ActualPrice), 0) AS 金额
FROM lq_kd_pxmx px
INNER JOIN lq_kd_kdjlb kd ON px.glkdbh = kd.F_Id AND kd.F_IsEffective = 1
WHERE px.F_IsEffective = 1
AND (px.F_ItemCategory = '科美' OR EXISTS (SELECT 1 FROM lq_xmzl zl WHERE zl.F_Id = px.px AND zl.F_IsEffective = 1 AND zl.qt2 = '科美'))
AND kd.kdrq >= @start
AND kd.kdrq <= @end;
-- 5) 查看 lq_kd_kjbsyj 中 F_ItemCategory 的分布（是否有空或非科美）
SELECT
COALESCE(k.F_ItemCategory, '(空)') AS 品项分类,
COUNT(*) AS 条数,
COALESCE(SUM(CAST(NULLIF(TRIM(k.kjblsyj), '') AS DECIMAL(18,2))), 0) AS 金额合计
FROM lq_kd_kjbsyj k
INNER JOIN lq_kd_kdjlb kd ON k.glkdbh = kd.F_Id AND kd.F_IsEffective = 1
WHERE k.F_IsEffective = 1
AND k.yjsj >= @start
AND k.yjsj <= @end
AND k.kjblsyj IS NOT NULL
AND TRIM(k.kjblsyj) != ''
AND TRIM(k.kjblsyj) != '0'
GROUP BY COALESCE(k.F_ItemCategory, '(空)');