科美业绩差异-剔除储扣后仍存在差异的开单.sql
4.66 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
-- ============================================================
-- 科美业绩差异：剔除「储扣一致」后的真实差异开单
-- 时间范围：2026-01-01 ~ 2026-01-22（与台账一致）
-- ============================================================
--
-- 【剔除规则】
-- 若 品项明细金额 - 科技部业绩 ≈ 该开单的储扣金额（全部储扣，不限于科美，误差<=0.02），
-- 视为储扣导致，从结果中剔除；只列出「剔除后仍存在差异」的开单。
--
-- 【本脚本包含两个查询】
-- ① 有差异的开单列表：只输出 开单ID、开单日期，用于把有差异的开单列出来；
-- ② 有差异的开单明细：输出 开单ID、开单日期、品项明细金额、科技部业绩金额、储扣金额_全部、差异，用于分析。
--
-- 【查询②结果列】开单ID | 开单日期 | 品项明细金额 | 科技部业绩金额 | 储扣金额_全部 | 差异
--
-- ============================================================
-- 一、有差异的开单列表（仅列开单ID与日期，便于核对）
SELECT
kd.F_Id AS 开单ID,
kd.kdrq 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 = '科美'
AND kd.kdrq >= '2026-01-01 00:00:00'
AND kd.kdrq <= '2026-01-22 23:59:59'
GROUP BY kd.F_Id, kd.kdrq
HAVING ABS(SUM(px.F_ActualPrice) - (SELECT COALESCE(SUM(CAST(NULLIF(TRIM(k2.kjblsyj), '') AS DECIMAL(18,2))), 0)
FROM lq_kd_kjbsyj k2
WHERE k2.glkdbh = kd.F_Id AND k2.F_IsEffective = 1 AND k2.F_ItemCategory = '科美'
AND k2.yjsj >= '2026-01-01 00:00:00' AND k2.yjsj <= '2026-01-22 23:59:59')) > 0.01
AND ABS((SUM(px.F_ActualPrice) - (SELECT COALESCE(SUM(CAST(NULLIF(TRIM(k2.kjblsyj), '') AS DECIMAL(18,2))), 0)
FROM lq_kd_kjbsyj k2
WHERE k2.glkdbh = kd.F_Id AND k2.F_IsEffective = 1 AND k2.F_ItemCategory = '科美'
AND k2.yjsj >= '2026-01-01 00:00:00' AND k2.yjsj <= '2026-01-22 23:59:59'))
- COALESCE((SELECT SUM(d.F_Amount) FROM lq_kd_deductinfo d
WHERE d.F_BillingId = kd.F_Id AND d.F_IsEffective = 1), 0)) > 0.02
ORDER BY kd.kdrq;
-- 二、有差异的开单明细（含金额与储扣，便于分析）
SELECT
kd.F_Id AS 开单ID,
kd.kdrq AS 开单日期,
ROUND(SUM(px.F_ActualPrice), 2) AS 品项明细金额,
ROUND((SELECT COALESCE(SUM(CAST(NULLIF(TRIM(k2.kjblsyj), '') AS DECIMAL(18,2))), 0)
FROM lq_kd_kjbsyj k2
WHERE k2.glkdbh = kd.F_Id AND k2.F_IsEffective = 1 AND k2.F_ItemCategory = '科美'
AND k2.yjsj >= '2026-01-01 00:00:00' AND k2.yjsj <= '2026-01-22 23:59:59'), 2) AS 科技部业绩金额,
ROUND(COALESCE((SELECT SUM(d.F_Amount) FROM lq_kd_deductinfo d
WHERE d.F_BillingId = kd.F_Id AND d.F_IsEffective = 1), 0), 2) AS 储扣金额_全部,
ROUND(SUM(px.F_ActualPrice) - (SELECT COALESCE(SUM(CAST(NULLIF(TRIM(k2.kjblsyj), '') AS DECIMAL(18,2))), 0)
FROM lq_kd_kjbsyj k2
WHERE k2.glkdbh = kd.F_Id AND k2.F_IsEffective = 1 AND k2.F_ItemCategory = '科美'
AND k2.yjsj >= '2026-01-01 00:00:00' AND k2.yjsj <= '2026-01-22 23:59:59'), 2) 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 = '科美'
AND kd.kdrq >= '2026-01-01 00:00:00'
AND kd.kdrq <= '2026-01-22 23:59:59'
GROUP BY kd.F_Id, kd.kdrq
HAVING ABS(SUM(px.F_ActualPrice) - (SELECT COALESCE(SUM(CAST(NULLIF(TRIM(k2.kjblsyj), '') AS DECIMAL(18,2))), 0)
FROM lq_kd_kjbsyj k2
WHERE k2.glkdbh = kd.F_Id AND k2.F_IsEffective = 1 AND k2.F_ItemCategory = '科美'
AND k2.yjsj >= '2026-01-01 00:00:00' AND k2.yjsj <= '2026-01-22 23:59:59')) > 0.01
AND ABS((SUM(px.F_ActualPrice) - (SELECT COALESCE(SUM(CAST(NULLIF(TRIM(k2.kjblsyj), '') AS DECIMAL(18,2))), 0)
FROM lq_kd_kjbsyj k2
WHERE k2.glkdbh = kd.F_Id AND k2.F_IsEffective = 1 AND k2.F_ItemCategory = '科美'
AND k2.yjsj >= '2026-01-01 00:00:00' AND k2.yjsj <= '2026-01-22 23:59:59'))
- COALESCE((SELECT SUM(d.F_Amount) FROM lq_kd_deductinfo d
WHERE d.F_BillingId = kd.F_Id AND d.F_IsEffective = 1), 0)) > 0.02
ORDER BY kd.kdrq;