用户子查询，用case

select  satisfy.STARTTIME,cc.C_CrmID,cc.C_Name ClientName,be.C_NAME,count(yskj.c_id) PhoneSum,sum(cast (satisfy.SATISFYCODE as int)) PointSum,
sum(case yskj.C_CallType when 1 then 1 else 0 end) AnswerSum,sum(case yskj.C_CallType when 1 then satisfy.SATISFYCODE else 0 end) AnswerPoint,
sum(case yskj.C_CallType when 2 then 1 else 0 end) DialSum,sum(case yskj.C_CallType when 2 then satisfy.SATISFYCODE else 0 end) DialPoint,
sum(case satisfy.SATISFYCODE when 1 then 1 else 0 end) SatisfySum,sum(case satisfy.SATISFYCODE when 2 then 1 else 0 end) CommonSum,
sum(case satisfy.SATISFYCODE when 3 then 1 else 0 end) NoSatisfySum,sum(case satisfy.SATISFYCODE when 0 then 1 else 0 end) NoEval
  from cti.dbo.CC_IVR_SATISFYINFO satisfy with(nolock)
left join cti.dbo.CTI_TBL_Contact_Base_YSKJ yskj with(nolock) on yskj.C_AgentID=satisfy.agentid and yskj.C_StartTime>=@begin and yskj.C_StartTime<=@end
and  satisfy.STARTTIME>=@begin and satisfy.STARTTIME<=dateadd(day,1,@end)
left join Base_Employee be with(nolock) on be.C_CODE=satisfy.agentid
left join CRM_Client cc with(nolock) on cc.C_CrmID=yskj.C_ClientID
group by satisfy.STARTTIME,cc.C_CrmID,cc.C_Name,be.C_NAME

转载于:https://www.cnblogs.com/honghong75042/p/4181879.html