这个问题 很奇怪 先发代码:
CREATE DEFINER=`root`@`localhost` PROCEDURE `total_count`(in DepartmentName2 varchar(255),
in STARTDealDateTime datetime,
in endDealDateTime datetime,
in TableName varchar(255))
BEGIN
drop table if exists total_score_tmpc;
create table if not exists total_score_tmpc
( Id int(11) NOT NULL AUTO_INCREMENT,
DepartmentName varchar(255) comment'机构名称',
StaffId varchar(32) comment'柜员号',
StaffName varchar(255) comment'员工姓名' ,
ClientNum int(11) comment'送客数' ,
EvaluateScore decimal(10,2) comment'评价分值' ,
EvaluateScoreDisCount decimal(10,2) comment'按权重折算后的评价分值' ,
Total decimal(10,2) comment'评价总分' ,
PRIMARY key (Id)
);
drop table if exists total_score_tmpD;
create table if not exists total_score_tmpD
( Id2 int(11) NOT NULL AUTO_INCREMENT,
DepartmentNameEnd varchar(255) comment'机构名称',
StaffId varchar(32) comment'柜员号',
StaffName varchar(255) comment'员工姓名' ,
CountPerHour decimal(10,2) comment'笔,每小时' ,
SkillScoreDisCount decimal(10,2) comment'按权重折算后的技能分值' ,
DealCount decimal(10,2) comment'交易笔数' ,
ValueCount decimal(10,2) comment'效益分值' ,
ValueCountDisCount decimal(10,2) comment'按权重折算后的效益分值' ,
Total decimal(10,2) comment'效益总分' ,
PRIMARY key (Id2)
);
drop table if exists total_score_tmp22;
create table total_score_tmp22
( Id int(11) NOT NULL AUTO_INCREMENT,
SunValue decimal(10,2) comment'求和后的值',
PRIMARY key (Id)
);
insert into total_score_tmp22(SunValue)
SELECT Sum(EvaluatePrice)
From evaluate_record
where EvaluateDateTime BETWEEN STARTDealDateTime and endDealDateTime
group by StaffId; drop table if exists total_score_tmp33;
create table total_score_tmp33
( Id int(11) NOT NULL AUTO_INCREMENT,
SunValue decimal(10,2) comment'求和后的值',
PRIMARY key (Id)
);
insert into total_score_tmp33(SunValue)
SELECT 3600/(Select DealAvgSeconds from deal_name_type where DealId='121300')*avg(deal_record.DealAvgSeconds/deal_record.WaitSeconds)
From deal_record
where DealDateTime BETWEEN STARTDealDateTime and endDealDateTime
group by StaffId; drop table if exists total_score_tmp44;
create table total_score_tmp44
( Id int(11) NOT NULL AUTO_INCREMENT,
SunValue decimal(10,2) comment'求和后的值',
PRIMARY key (Id)
); insert into total_score_tmp44(SunValue)
SELECT Sum(DealValue)
From deal_record
where DealDateTime BETWEEN STARTDealDateTime and endDealDateTime
group by StaffId; 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;
drop table if exists total_score_tmp9;
create table if not exists total_score_tmp9
( Id int(11) NOT NULL AUTO_INCREMENT,
DepartmentName varchar(255) comment'机构名称',
StaffId varchar(32) comment'柜员号',
StaffName varchar(255) comment'员工姓名' ,
ClientNum int(11) comment'送客数' ,
EvaluateScore decimal(10,2) comment'评价分值' ,
EvaluateScoreDisCount decimal(10,2) comment'按权重折算后的评价分值' ,
CountPerHour decimal(10,1) comment'笔,每小时' ,
SkillScoreDisCount decimal(10,2) comment'按权重折算后的技能分值' ,
DealCount decimal(10,2) comment'交易笔数' ,
ValueCount decimal(10,2) comment'效益分值' ,
ValueCountDisCount decimal(10,2) comment'按权重折算后的效益分值' ,
Total decimal(10,2) comment'总分' ,
PRIMARY key (Id)
); insert into total_score_tmp9
(DepartmentName ,
StaffId,
StaffName ,
ClientNum ,
EvaluateScore ,
EvaluateScoreDisCount ,
CountPerHour ,
SkillScoreDisCount ,
DealCount ,
ValueCount ,
ValueCountDisCount
)
select DepartmentNameEnd ,total_score_tmpD.StaffId,
total_score_tmpD.StaffName,
Case When ClientNum is not null then ClientNum else 0 end ,
Case when EvaluateScore is not null then EvaluateScore else 0 end ,
Case when EvaluateScoreDisCount is not null then EvaluateScoreDisCount else 0 end ,
Case when CountPerHour is not null then CountPerHour else 0 end,
Case when SkillScoreDisCount is not null then SkillScoreDisCount else 0 end,
Case when DealCount is not null then DealCount else 0 end,
Case when ValueCount is not null then ValueCount else 0 end,
Case when ValueCountDisCount is not null then ValueCountDisCount else 0 end
From total_score_tmpD left outer join total_score_tmpc ON total_score_tmpc.StaffName = total_score_tmpD.StaffName
Union
select DepartmentNameEnd , total_score_tmpD.StaffId,
total_score_tmpD.StaffName,
Case When ClientNum is not null then ClientNum else 0 end ,
Case when EvaluateScore is not null then EvaluateScore else 0 end ,
Case when EvaluateScoreDisCount is not null then EvaluateScoreDisCount else 0 end ,
Case when CountPerHour is not null then CountPerHour else 0 end,
Case when SkillScoreDisCount is not null then SkillScoreDisCount else 0 end,
Case when DealCount is not null then DealCount else 0 end,
Case when ValueCount is not null then ValueCount else 0 end,
Case when ValueCountDisCount is not null then ValueCountDisCount else 0 end
From total_score_tmpD RIGHT outer join total_score_tmpc ON total_score_tmpc.StaffName = total_score_tmpD.StaffName; drop table if exists total_score_tmpAll;
create table if not exists total_score_tmpAll
( Id int(11) NOT NULL AUTO_INCREMENT,
DepartmentName varchar(255) comment'机构名称',
StaffId varchar(32) comment'柜员号',
StaffName varchar(255) comment'员工姓名' ,
ClientNum int(11) comment'送客数' ,
EvaluateScore decimal(10,2) comment'评价分值' ,
EvaluateScoreDisCount decimal(10,2) comment'按权重折算后的评价分值' ,
CountPerHour decimal(10,2) comment'笔,每小时' ,
SkillScoreDisCount decimal(10,2) comment'按权重折算后的技能分值' ,
DealCount decimal(10,2) comment'交易笔数' ,
ValueCount decimal(10,2) comment'效益分值' ,
ValueCountDisCount decimal(10,2) comment'按权重折算后的效益分值' ,
Total decimal(10,2) comment'总分' ,
PRIMARY key (Id)
); insert into total_score_tmpAll
( DepartmentName ,
StaffId,
StaffName ,
ClientNum ,
EvaluateScore ,
EvaluateScoreDisCount ,
CountPerHour ,
SkillScoreDisCount ,
DealCount ,
ValueCount ,
ValueCountDisCount,
Total
)
select DepartmentName,StaffId,StaffName ,ClientNum , EvaluateScore ,EvaluateScoreDisCount ,
CountPerHour , SkillScoreDisCount , DealCount , ValueCount , ValueCountDisCount,
EvaluateScoreDisCount + SkillScoreDisCount + ValueCountDisCount
From total_score_tmp9;#alter table total_score_tmp order by Total DESC;
drop table if exists total_score_tmp;
create table if not exists total_score_tmp
( Id int(11) NOT NULL AUTO_INCREMENT,
DepartmentName varchar(255) comment'机构名称',
StaffId varchar(32) comment'柜员号',
StaffName varchar(255) comment'员工姓名' ,
ClientNum int(11) comment'送客数' ,
EvaluateScore decimal(10,2) comment'评价分值' ,
EvaluateScoreDisCount decimal(10,2) comment'按权重折算后的评价分值' ,
CountPerHour decimal(10,2) comment'笔,每小时' ,
SkillScoreDisCount decimal(10,2) comment'按权重折算后的技能分值' ,
DealCount decimal(10,2) comment'交易笔数' ,
ValueCount decimal(10,2) comment'效益分值' ,
ValueCountDisCount decimal(10,2) comment'按权重折算后的效益分值' ,
Total decimal(10,2) comment'总分' ,
PRIMARY key (Id)
); insert into total_score_tmp
( DepartmentName ,
StaffId ,
StaffName ,
ClientNum ,
EvaluateScore ,
EvaluateScoreDisCount ,
CountPerHour ,
SkillScoreDisCount ,
DealCount ,
ValueCount ,
ValueCountDisCount,
Total
)
select DepartmentName ,StaffId, StaffName ,ClientNum ,EvaluateScore ,EvaluateScoreDisCount ,CountPerHour ,SkillScoreDisCount ,
DealCount ,ValueCount , ValueCountDisCount, Total
From total_score_tmpAll
Order by Total DESC; select Id,
DepartmentName AS '机构名称',
StaffName AS '员工姓名',
ClientNum AS '送客数',
EvaluateScore AS '评价分值',
EvaluateScoreDisCount AS '按权重折算后的评价分值',
CountPerHour AS '笔,每小时',
SkillScoreDisCount AS '按权重折算后的技能分值',
DealCount AS '交易笔数' ,
ValueCount AS '效益分值' ,
ValueCountDisCount AS '按权重折算后的效益分值',
Total AS '总分'
From total_score_tmp;
#Order by Total DESC;
end;问题说明:
当插入数据的表total_score_tmpc 在前边 total_score_tmpc 里边的EvaluateScoreDisCount 值就为0
当改为先对total_score_tmpD 插入 再插入total_score_tmpc 就正常我找不到这是什么原因?
CREATE DEFINER=`root`@`localhost` PROCEDURE `total_count`(in DepartmentName2 varchar(255),
in STARTDealDateTime datetime,
in endDealDateTime datetime,
in TableName varchar(255))
BEGIN
drop table if exists total_score_tmpc;
create table if not exists total_score_tmpc
( Id int(11) NOT NULL AUTO_INCREMENT,
DepartmentName varchar(255) comment'机构名称',
StaffId varchar(32) comment'柜员号',
StaffName varchar(255) comment'员工姓名' ,
ClientNum int(11) comment'送客数' ,
EvaluateScore decimal(10,2) comment'评价分值' ,
EvaluateScoreDisCount decimal(10,2) comment'按权重折算后的评价分值' ,
Total decimal(10,2) comment'评价总分' ,
PRIMARY key (Id)
);
drop table if exists total_score_tmpD;
create table if not exists total_score_tmpD
( Id2 int(11) NOT NULL AUTO_INCREMENT,
DepartmentNameEnd varchar(255) comment'机构名称',
StaffId varchar(32) comment'柜员号',
StaffName varchar(255) comment'员工姓名' ,
CountPerHour decimal(10,2) comment'笔,每小时' ,
SkillScoreDisCount decimal(10,2) comment'按权重折算后的技能分值' ,
DealCount decimal(10,2) comment'交易笔数' ,
ValueCount decimal(10,2) comment'效益分值' ,
ValueCountDisCount decimal(10,2) comment'按权重折算后的效益分值' ,
Total decimal(10,2) comment'效益总分' ,
PRIMARY key (Id2)
);
drop table if exists total_score_tmp22;
create table total_score_tmp22
( Id int(11) NOT NULL AUTO_INCREMENT,
SunValue decimal(10,2) comment'求和后的值',
PRIMARY key (Id)
);
insert into total_score_tmp22(SunValue)
SELECT Sum(EvaluatePrice)
From evaluate_record
where EvaluateDateTime BETWEEN STARTDealDateTime and endDealDateTime
group by StaffId; drop table if exists total_score_tmp33;
create table total_score_tmp33
( Id int(11) NOT NULL AUTO_INCREMENT,
SunValue decimal(10,2) comment'求和后的值',
PRIMARY key (Id)
);
insert into total_score_tmp33(SunValue)
SELECT 3600/(Select DealAvgSeconds from deal_name_type where DealId='121300')*avg(deal_record.DealAvgSeconds/deal_record.WaitSeconds)
From deal_record
where DealDateTime BETWEEN STARTDealDateTime and endDealDateTime
group by StaffId; drop table if exists total_score_tmp44;
create table total_score_tmp44
( Id int(11) NOT NULL AUTO_INCREMENT,
SunValue decimal(10,2) comment'求和后的值',
PRIMARY key (Id)
); insert into total_score_tmp44(SunValue)
SELECT Sum(DealValue)
From deal_record
where DealDateTime BETWEEN STARTDealDateTime and endDealDateTime
group by StaffId; 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;
drop table if exists total_score_tmp9;
create table if not exists total_score_tmp9
( Id int(11) NOT NULL AUTO_INCREMENT,
DepartmentName varchar(255) comment'机构名称',
StaffId varchar(32) comment'柜员号',
StaffName varchar(255) comment'员工姓名' ,
ClientNum int(11) comment'送客数' ,
EvaluateScore decimal(10,2) comment'评价分值' ,
EvaluateScoreDisCount decimal(10,2) comment'按权重折算后的评价分值' ,
CountPerHour decimal(10,1) comment'笔,每小时' ,
SkillScoreDisCount decimal(10,2) comment'按权重折算后的技能分值' ,
DealCount decimal(10,2) comment'交易笔数' ,
ValueCount decimal(10,2) comment'效益分值' ,
ValueCountDisCount decimal(10,2) comment'按权重折算后的效益分值' ,
Total decimal(10,2) comment'总分' ,
PRIMARY key (Id)
); insert into total_score_tmp9
(DepartmentName ,
StaffId,
StaffName ,
ClientNum ,
EvaluateScore ,
EvaluateScoreDisCount ,
CountPerHour ,
SkillScoreDisCount ,
DealCount ,
ValueCount ,
ValueCountDisCount
)
select DepartmentNameEnd ,total_score_tmpD.StaffId,
total_score_tmpD.StaffName,
Case When ClientNum is not null then ClientNum else 0 end ,
Case when EvaluateScore is not null then EvaluateScore else 0 end ,
Case when EvaluateScoreDisCount is not null then EvaluateScoreDisCount else 0 end ,
Case when CountPerHour is not null then CountPerHour else 0 end,
Case when SkillScoreDisCount is not null then SkillScoreDisCount else 0 end,
Case when DealCount is not null then DealCount else 0 end,
Case when ValueCount is not null then ValueCount else 0 end,
Case when ValueCountDisCount is not null then ValueCountDisCount else 0 end
From total_score_tmpD left outer join total_score_tmpc ON total_score_tmpc.StaffName = total_score_tmpD.StaffName
Union
select DepartmentNameEnd , total_score_tmpD.StaffId,
total_score_tmpD.StaffName,
Case When ClientNum is not null then ClientNum else 0 end ,
Case when EvaluateScore is not null then EvaluateScore else 0 end ,
Case when EvaluateScoreDisCount is not null then EvaluateScoreDisCount else 0 end ,
Case when CountPerHour is not null then CountPerHour else 0 end,
Case when SkillScoreDisCount is not null then SkillScoreDisCount else 0 end,
Case when DealCount is not null then DealCount else 0 end,
Case when ValueCount is not null then ValueCount else 0 end,
Case when ValueCountDisCount is not null then ValueCountDisCount else 0 end
From total_score_tmpD RIGHT outer join total_score_tmpc ON total_score_tmpc.StaffName = total_score_tmpD.StaffName; drop table if exists total_score_tmpAll;
create table if not exists total_score_tmpAll
( Id int(11) NOT NULL AUTO_INCREMENT,
DepartmentName varchar(255) comment'机构名称',
StaffId varchar(32) comment'柜员号',
StaffName varchar(255) comment'员工姓名' ,
ClientNum int(11) comment'送客数' ,
EvaluateScore decimal(10,2) comment'评价分值' ,
EvaluateScoreDisCount decimal(10,2) comment'按权重折算后的评价分值' ,
CountPerHour decimal(10,2) comment'笔,每小时' ,
SkillScoreDisCount decimal(10,2) comment'按权重折算后的技能分值' ,
DealCount decimal(10,2) comment'交易笔数' ,
ValueCount decimal(10,2) comment'效益分值' ,
ValueCountDisCount decimal(10,2) comment'按权重折算后的效益分值' ,
Total decimal(10,2) comment'总分' ,
PRIMARY key (Id)
); insert into total_score_tmpAll
( DepartmentName ,
StaffId,
StaffName ,
ClientNum ,
EvaluateScore ,
EvaluateScoreDisCount ,
CountPerHour ,
SkillScoreDisCount ,
DealCount ,
ValueCount ,
ValueCountDisCount,
Total
)
select DepartmentName,StaffId,StaffName ,ClientNum , EvaluateScore ,EvaluateScoreDisCount ,
CountPerHour , SkillScoreDisCount , DealCount , ValueCount , ValueCountDisCount,
EvaluateScoreDisCount + SkillScoreDisCount + ValueCountDisCount
From total_score_tmp9;#alter table total_score_tmp order by Total DESC;
drop table if exists total_score_tmp;
create table if not exists total_score_tmp
( Id int(11) NOT NULL AUTO_INCREMENT,
DepartmentName varchar(255) comment'机构名称',
StaffId varchar(32) comment'柜员号',
StaffName varchar(255) comment'员工姓名' ,
ClientNum int(11) comment'送客数' ,
EvaluateScore decimal(10,2) comment'评价分值' ,
EvaluateScoreDisCount decimal(10,2) comment'按权重折算后的评价分值' ,
CountPerHour decimal(10,2) comment'笔,每小时' ,
SkillScoreDisCount decimal(10,2) comment'按权重折算后的技能分值' ,
DealCount decimal(10,2) comment'交易笔数' ,
ValueCount decimal(10,2) comment'效益分值' ,
ValueCountDisCount decimal(10,2) comment'按权重折算后的效益分值' ,
Total decimal(10,2) comment'总分' ,
PRIMARY key (Id)
); insert into total_score_tmp
( DepartmentName ,
StaffId ,
StaffName ,
ClientNum ,
EvaluateScore ,
EvaluateScoreDisCount ,
CountPerHour ,
SkillScoreDisCount ,
DealCount ,
ValueCount ,
ValueCountDisCount,
Total
)
select DepartmentName ,StaffId, StaffName ,ClientNum ,EvaluateScore ,EvaluateScoreDisCount ,CountPerHour ,SkillScoreDisCount ,
DealCount ,ValueCount , ValueCountDisCount, Total
From total_score_tmpAll
Order by Total DESC; select Id,
DepartmentName AS '机构名称',
StaffName AS '员工姓名',
ClientNum AS '送客数',
EvaluateScore AS '评价分值',
EvaluateScoreDisCount AS '按权重折算后的评价分值',
CountPerHour AS '笔,每小时',
SkillScoreDisCount AS '按权重折算后的技能分值',
DealCount AS '交易笔数' ,
ValueCount AS '效益分值' ,
ValueCountDisCount AS '按权重折算后的效益分值',
Total AS '总分'
From total_score_tmp;
#Order by Total DESC;
end;问题说明:
当插入数据的表total_score_tmpc 在前边 total_score_tmpc 里边的EvaluateScoreDisCount 值就为0
当改为先对total_score_tmpD 插入 再插入total_score_tmpc 就正常我找不到这是什么原因?
解决方案 »
- mysql中这种情况是否可以使用触发器解决
- 查询任意日期同一周的记录语句
- jsp+mysql数据库连接内存溢出问题??
- 数据库连接成功后遇到的问题
- postgresql 中怎么查看table所属哪个tablespace ?
- 问3个postgresql的函数,急
- 求个更好的解决办法~~~?
- 怎么样配置weblogic和MySql的连接?
- 有什么办法能批量修改数据库中的数据表名吗?
- mysql sum求和 字段为double类型 精度不对
- 为什么MYSQL的INNER JOIN没有效果,变成了LEFT JOIN的效果呢? 有完整SQL语句
- MYSQL一个奇怪的问题,当我的Select语句,在外层再包一个Select时候,数据竟然完全变了,有完整的SQL示例代码
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; 精简了啊