MySQL批量插入查询出来的数据

1. movie_detail_tab

2. movie_tab

3. movie_detail_tab

1. SUBSTRING_INDEX(str,delim,count)

2. IF()

在MySQL中IF()函数的用法类似于java中的三目运算符

IF(expr1,expr2,expr3)，如果expr1的值为true，则返回expr2的值，如果expr1的值为false，则返回expr3的值。

3. CONCAT()

CONCAT(str1,str2,…)

返回结果为连接参数产生的字符串。如有任何一个参数为NULL ，则返回值为 NULL。

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

mysql中插入数据成功了但是查询数据时只显示部分数据的解决办法是查看表里是否存在与插入数据一样的主键。mysql中插入数据，主键不容许有重复的，key字段是主键，主键必须唯一，重复了必然写不进去，只显示部分数据。

---