【SQL】统一训练平台数据库实践--20230927

储存过程vlookup_peopledata_csodtraining

默认导出用今天批次的数据进行join on，先删除过渡表的资料，再将查询结果放在过渡表中。

BEGIN
   DECLARE startdate varchar(50);
   SET startdate = date_format(NOW(),'%Y%m%d');

   DELETE FROM season.csod_data2;
   INSERT into season.csod_data2
		(
	    site,
	    plant,
	    deptid,
	    emplid,
	    name,
	    name_a,
	    hire_dt,
	    location,
	    jobtitle_descr,
	    officer_level_a,
	    supervisor_id,
	    labor_type,
	    grade,
	    sex,
	    lo_title,
	    object_type,
	    user_lo_assigned_dt,
	    user_lo_comp_dt,
	    user_lo_status
		)
	SELECT 
	    a.site,
	    a.plant,
	    a.deptid,
	    a.emplid,
	    a.name,
	    a.name_a,
	    a.hire_dt,
	    a.location,
	    a.jobtitle_descr,
	    a.officer_level_a,
	    a.supervisor_id,
	    a.labor_type,
	    a.grade,
	    a.sex,
	    b.lo_title,
	    b.object_type,
	    b.user_lo_assigned_dt,
	    b.user_lo_comp_dt,
	    b.user_lo_status
	FROM season.people_data AS a 
	LEFT JOIN season.csod_employee_training_detail_g AS b ON (a.emplid = b.emplid)
	WHERE left(a.batchid,8)=startdate and left(b.batchid,8)=startdate
	#WHERE left(a.batchid,8)='20230926' and left(b.batchid,8)='20230926'
	#WHERE a.batchid = '20230926041509422' and b.batchid = '20230926092000010'
	#limit 30
	;
END
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

调用储存过程 CALL vlookup_peopledata_csodtraining();

视图view_peopleData_join_csodEmployeeTrainingDetailG

用view将查询条件存起来，需要时再查询。

CREATE VIEW view_peopleData_join_csodEmployeeTrainingDetailG AS 
SELECT 
    a.site,
    a.plant,
    a.deptid,
    a.emplid,
    a.name,
    a.name_a,
    a.hire_dt,
    a.location,
    a.jobtitle_descr,
    a.officer_level_a,
    a.supervisor_id,
    a.labor_type,
    a.grade,
    a.sex,
    b.lo_title,
    b.object_type,
    b.user_lo_assigned_dt,
    b.user_lo_comp_dt,
    b.user_lo_status
FROM season.people_data AS a 
LEFT JOIN season.csod_employee_training_detail_g AS b ON (a.emplid = b.emplid)
WHERE a.batchid = '20230926041509422'

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

建表

1.使用较小容量的数据类型
2.建立索引
3.不必要的字段请删掉