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

储存过程vlookup_peopledata_csodtraining

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

BEGINDECLARE 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_statusFROM 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

调用储存过程 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.不必要的字段请删掉