Question

mysql存储过程实现数据查询与插入

数据表名sys_users:字段:id,ref_group_id,phone,decri,通过存储过程传入ref_group_id,decri两个参数,在sys_users中查出传入的ref_group_id的所有的数据,对传入的参数decri进行判断是否包含#系统#(为系统关键字),将根据传入参数ref_group_id查询的结果(如:select ref_group_id,decri from sys_users where ref_group_id in (1,2,3))查询返回的数据对这些结果的decri列进行判断是否包含#系统#,如果包含进行替换为A,并插入到sys_tem表,没有则不进行插入操作,需要记录插入的条数与未插入的条数
具体细节联系我QQ:286898781

Answer 1

INSERT into total_score_tmpD(
DepartmentNameEnd, StaffId,
StaffName,
CountPerHour,
SkillScoreDisCount ,
DealCount ,
ValueCount,
ValueCountDisCount
)
SELECT DepartmentName, StaffId,
StaffName,
3600/(Select DealAvgSeconds from deal_name_type where DealId='121300')*AVG(DealAvgSeconds/WaitSeconds) ,
Round(3600/(Select DealAvgSeconds from deal_name_type where DealId='121300')*avg(DealAvgSeconds/WaitSeconds)/(select Max(SunValue) From total_score_tmp33)*(select CAST(OptionValue as decimal) from sys_info WHERE OptionName='工作技能权重'),2),
count(StaffId),
Sum(DealValue),
Round(Sum(DealValue)/(select Max(SunValue) From total_score_tmp44)*(select CAST(OptionValue as decimal) from sys_info WHERE OptionName='工作效益权重'),2)
From deal_record
where DepartmentName like concat(DepartmentName2,'%') and DealDateTime BETWEEN STARTDealDateTime and endDealDateTime
group by StaffId;

insert into total_score_tmpc(DepartmentName , StaffId,
StaffName,
ClientNum ,
EvaluateScore ,
EvaluateScoreDisCount)
SELECT DepartmentName, StaffId,
StaffName,
count(StaffId),
Sum(EvaluatePrice),
Round((Sum(EvaluatePrice)/(select Max(SunValue) From total_score_tmp22))*(select CAST(OptionValue as decimal) from sys_info WHERE OptionName='服务评价权重'),2)
From evaluate_record
where DepartmentName like concat(DepartmentName2,'%') and EvaluateDateTime BETWEEN STARTDealDateTime and endDealDateTime
group by StaffId;