[已用]创建科技部老师业绩统计视图.sql
2.75 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
-- 创建科技部老师业绩流水视图
-- 包含所有业务类型的流水记录，并包含对应的信息表ID和品项次数
CREATE OR REPLACE VIEW v_tech_teacher_flow AS
SELECT
teacher_id,
teacher_name,
teacher_account,
business_type,
achievement,
labor_cost,
project_count,
customer_id,
customer_name,
business_date,
source_id,
source_table,
item_count
FROM (
-- 开卡流水
SELECT
k.kjbls as teacher_id,
k.kjblsxm as teacher_name,
k.kjblszh as teacher_account,
'开卡' as business_type,
CAST(COALESCE(k.kjblsyj, 0) AS DECIMAL(10,2)) as achievement,
CAST(COALESCE(k.F_LaborCost, 0) AS DECIMAL(10,2)) as labor_cost,
CAST(COALESCE(pm.F_ProjectNumber, 0) AS DECIMAL(10,2)) as project_count,
o.kdhy as customer_id,
o.kdhyc as customer_name,
k.yjsj as business_date,
k.F_kdpxid as source_id,
'lq_kd_pxmx' as source_table,
COALESCE(pm.F_ProjectNumber, 0) as item_count
FROM lq_kd_kjbsyj k
LEFT JOIN lq_kd_kdjlb o ON k.glkdbh = o.F_Id
LEFT JOIN lq_kd_pxmx pm ON k.F_kdpxid = pm.F_Id
WHERE k.kjbls IS NOT NULL AND k.kjblsxm IS NOT NULL
UNION ALL
-- 耗卡流水
SELECT
x.kjbls as teacher_id,
x.kjblsxm as teacher_name,
x.kjblszh as teacher_account,
'耗卡' as business_type,
CAST(COALESCE(x.kjblsyj, 0) AS DECIMAL(10,2)) as achievement,
CAST(COALESCE(x.F_LaborCost, 0) AS DECIMAL(10,2)) as labor_cost,
CAST(COALESCE(pm.F_ProjectNumber, 0) AS DECIMAL(10,2)) as project_count,
h.hy as customer_id,
h.hymc as customer_name,
x.yjsj as business_date,
x.F_hkpxid as source_id,
'lq_xh_pxmx' as source_table,
COALESCE(pm.F_ProjectNumber, 0) as item_count
FROM lq_xh_kjbsyj x
LEFT JOIN lq_xh_hyhk h ON x.glkdbh = h.F_Id
LEFT JOIN lq_xh_pxmx pm ON x.F_hkpxid = pm.F_Id
WHERE x.kjbls IS NOT NULL AND x.kjblsxm IS NOT NULL
UNION ALL
-- 退卡流水
SELECT
r.kjbls as teacher_id,
r.kjblsxm as teacher_name,
r.kjblszh as teacher_account,
'退卡' as business_type,
CAST(COALESCE(r.kjblsyj, 0) AS DECIMAL(10,2)) as achievement,
CAST(COALESCE(r.F_LaborCost, 0) AS DECIMAL(10,2)) as labor_cost,
CAST(COALESCE(r.F_tkpxNumber, 0) AS DECIMAL(10,2)) as project_count,
t.hy as customer_id,
t.hymc as customer_name,
r.tksj as business_date,
r.F_Id as source_id,
'lq_hytk_kjbsyj' as source_table,
0 as item_count
FROM lq_hytk_kjbsyj r
LEFT JOIN lq_hytk_hytk t ON r.gltkbh = t.F_Id
WHERE r.kjbls IS NOT NULL AND r.kjblsxm IS NOT NULL AND (r.F_DeleteMark IS NULL OR r.F_DeleteMark = 0)
) t;