获取绩效统计列表

要求：绩效得分分为自评、平级评、上级评，其中自评占总分比例20%，平级评占总分比例20%，上级评评分占总分比例60%（比例每期不一样，可分配），要求展示序列号、姓名、部门、自评得分、平级评分、上级评分、总分。其中平级评人数大于3的要求去掉一个最高分，去掉一个最低分。

思考两天得出解决方案：

select row_number() over (order by UserDepartment) as XuHao,UserId, UserName as XingMing, UserDepartment as BuMen, CEILING(SUM(zp)) as Ziping , CEILING(sum(hp)) as HuPing , CEILING(SUM(lp)) as LiDao, CEILING((SUM(zp)*20/100+ CEILING(sum(hp))*20/100+ SUM(lp)*60/100)) as jixiaozongfen from ( select UserId, UserName , UserDepartment, SUM(ZongFen) AS zp, 0 as hp, 0 as lp from dbo.ERPJiXiaoDaFenBiao where jibie=1 and [QiCiID]=9 group by UserId,UserName,UserDepartment union all select UserId,UserName, UserDepartment , 0 as zp, case when COUNT(*)>3 then ((SUM([CWBbgl])-MAX([CWBbgl])-MIN([CWBbgl])) +SUM([CWXjgl])-MAX([CWXjgl])-MIN([CWXjgl]) +(SUM([CWJsgl])-MAX([CWJsgl])-MIN([CWJsgl])) +(SUM([CWGztd])-MAX([CWGztd])-MIN([CWGztd])) +(SUM([CWJjwt])-MAX([CWJjwt])-MIN([CWJjwt])) +(SUM([CWHbgz])-MAX([CWHbgz])-MIN([CWHbgz])) +(SUM([CWGtnl])-MAX([CWGtnl])-MIN([CWGtnl])) )/(COUNT(*)-2 ) else sum([CWBbgl]+[CWXjgl]+[CWJsgl]+[CWGztd]+[CWJjwt]+[CWHbgz]+[CWGtnl])/COUNT(*) end as hp , 0 as lp from dbo.ERPJiXiaoDaFenBiao where JiBie=2 and [QiCiID]=9 group by UserId,UserName,UserDepartment union all select UserId,UserName, UserDepartment , 0 as zp, case when COUNT(*)>3 then ((SUM([JCGzrw])-MAX([JCGzrw])-MIN([JCGzrw])) +SUM([JCClnl])-MAX([JCClnl])-MIN([JCClnl]) +(SUM([JCGzjn])-MAX([JCGzjn])-MIN([JCGzjn])) +(SUM([JCGzxt])-MAX([JCGzxt])-MIN([JCGzxt])) +(SUM([JCZrg])-MAX([JCZrg])-MIN([JCZrg])) +(SUM([JCKhmy])-MAX([JCKhmy])-MIN([JCKhmy])) +(SUM([JCGzzl])-MAX([JCGzzl])-MIN([JCGzzl])) +(SUM([JCJlx])-MAX([JCJlx])-MIN([JCJlx])) )/(COUNT(*)-2 ) else sum([JCGzrw]+[JCClnl]+[JCGzjn]+[JCGzxt]+[JCZrg]+[JCKhmy]+[JCGzzl]+[JCJlx])/COUNT(*) end as hp , 0 as lp from dbo.ERPJiXiaoDaFenBiao where JiBie=2 and [QiCiID]=9 group by UserId,UserName,UserDepartment union all select UserId,UserName, UserDepartment , 0 as zp , case when COUNT(*)>3 then ((SUM([RLZdjs])-MAX([RLZdjs])-MIN([RLZdjs])) +SUM([RLZpqk])-MAX([RLZpqk])-MIN([RLZpqk]) +(SUM([RLPxqk])-MAX([RLPxqk])-MIN([RLPxqk])) +(SUM([RLJxqk])-MAX([RLJxqk])-MIN([RLJxqk])) +(SUM([RLXcgl])-MAX([RLXcgl])-MIN([RLXcgl])) +(SUM([RLGztd])-MAX([RLGztd])-MIN([RLGztd])) +(SUM([RLGtnl])-MAX([RLGtnl])-MIN([RLGtnl])) +(SUM([RLJlx])-MAX([RLJlx])-MIN([RLJlx])) +(SUM([RLJjwt])-MAX([RLJjwt])-MIN([RLJjwt])) +(SUM([RLXtnl])-MAX([RLXtnl])-MIN([RLXtnl])) )/(COUNT(*)-2 ) else sum([RLZdjs]+[RLZpqk]+[RLPxqk]+[RLJxqk]+[RLXcgl]+[RLGztd]+[RLGtnl]+[RLJlx]+[RLJjwt]+[RLXtnl])/COUNT(*) end as hp , 0 as lp from dbo.ERPJiXiaoDaFenBiao where JiBie=2 and [QiCiID]=9 group by UserId,UserName,UserDepartment union all select UserId,UserName, UserDepartment , 0 as zp, case when COUNT(*)>3 then ((SUM([SCKhgx])-MAX([SCKhgx])-MIN([SCKhgx])) +SUM([SCXmbs])-MAX([SCXmbs])-MIN([SCXmbs]) +(SUM([SCSqzc])-MAX([SCSqzc])-MIN([SCSqzc])) +(SUM([SCSclj])-MAX([SCSclj])-MIN([SCSclj])) +(SUM([SCCPRs])-MAX([SCCprs])-MIN([SCCprs])) +(SUM([SCGztd])-MAX([SCGztd])-MIN([SCGztd])) +(SUM([SCJlx])-MAX([SCJlx])-MIN([SCJlx])) +(SUM([SCGtnl])-MAX([SCGtnl])-MIN([SCGtnl])) +(SUM([SCJjwt])-MAX([SCJjwt])-MIN([SCJjwt])) +(SUM([SCPhd])-MAX([SCPhd])-MIN([SCPhd])) )/(COUNT(*)-2 ) else sum([SCKhgx]+[SCXmbs]+[SCSqzc]+[SCSclj]+[SCCprs]+[SCGztd]+[SCJlx]+[SCGtnl]+[SCJjwt]+[SCPhd])/COUNT(*) end as hp , 0 as lp from dbo.ERPJiXiaoDaFenBiao where JiBie=2 and [QiCiID]=9 group by UserId,UserName,UserDepartment union all select UserId,UserName, UserDepartment , 0 as zp, case when COUNT(*)>3 then ((SUM([SSGzrw])-MAX([SSGzrw])-MIN([SSGzrw])) +SUM([SSClnl])-MAX([SSClnl])-MIN([SSClnl]) +(SUM([SSGzjn])-MAX([SSGzjn])-MIN([SSGzjn])) +(SUM([SSGzxt])-MAX([SSGzxt])-MIN([SSGzxt])) +(SUM([SSZrg])-MAX([SSZrg])-MIN([SSZrg])) +(SUM([SSKhmy])-MAX([SSKhmy])-MIN([SSKhmy])) +(SUM([SSGzzl])-MAX([SSGzzl])-MIN([SSGzzl])) +(SUM([SSJlx])-MAX([SSJlx])-MIN([SSJlx])) )/(COUNT(*)-2 ) else sum([SSGzrw]+[SSClnl]+[SSGzjn]+[SSGzxt]+[SSZrg]+[SSKhmy]+[SSGzzl]+[SSJlx])/COUNT(*) end as hp , 0 as lp from dbo.ERPJiXiaoDaFenBiao where JiBie=2 and [QiCiID]=9 group by UserId,UserName,UserDepartment union all select UserId,UserName, UserDepartment , 0 as zp, case when COUNT(*)>3 then ((SUM([SJJscc])-MAX([SJJscc])-MIN([SJJscc])) +SUM([SJClwh])-MAX([SJClwh])-MIN([SJClwh]) +(SUM([SJClbx])-MAX([SJClbx])-MIN([SJClbx])) +(SUM([SJZsjt])-MAX([SJZsjt])-MIN([SJZsjt])) +(SUM([SJKhjd])-MAX([SJKhjd])-MIN([SJKhjd])) +(SUM([SJCbys])-MAX([SJCbys])-MIN([SJCbys])) +(SUM([SJGztd])-MAX([SJGztd])-MIN([SJGztd])) +(SUM([SJZydd])-MAX([SJZydd])-MIN([SJZydd])) +(SUM([SJJlx])-MAX([SJJlx])-MIN([SJJlx])) +(SUM([SJGtnl])-MAX([SJGtnl])-MIN([SJGtnl])) )/(COUNT(*)-2 ) else sum([SJJscc]+[SJClwh]+[SJClbx]+[SJZsjt]+[SJKhjd]+[SJCbys]+[SJGztd]+[SJZydd]+[SJJlx]+[SJGtnl])/COUNT(*) end as hp , 0 as lp from dbo.ERPJiXiaoDaFenBiao where JiBie=2 and [QiCiID]=9 group by UserId,UserName,UserDepartment union all select UserId,UserName, UserDepartment , 0 as zp, case when COUNT(*)>3 then ((SUM([QTYwzs])-MAX([QTYwzs])-MIN([QTYwzs])) +SUM([QTZjdh])-MAX([QTZjdh])-MIN([QTZjdh]) +(SUM([QTJdlf])-MAX([QTJdlf])-MIN([QTJdlf])) +(SUM([QTSfwj])-MAX([QTSfwj])-MIN([QTSfwj])) +(SUM([QTKqgl])-MAX([QTKqgl])-MIN([QTKqgl])) +(SUM([QTZpgl])-MAX([QTZpgl])-MIN([QTZpgl])) +(SUM([QTSbgl])-MAX([QTSbgl])-MIN([QTSbgl])) +(SUM([QTGztd])-MAX([QTGztd])-MIN([QTGztd])) +(SUM([QTFwys])-MAX([QTFwys])-MIN([QTFwys])) +(SUM([QTjlx])-MAX([QTjlx])-MIN([QTjlx])) )/(COUNT(*)-2 ) else sum([QTYwzs]+[QTZjdh]+[QTJdlf]+[QTSfwj]+[QTKqgl]+[QTZpgl]+[QTSbgl]+[QTGztd]+[QTFwys]+[QTjlx])/COUNT(*) end as hp , 0 as lp from dbo.ERPJiXiaoDaFenBiao where JiBie=2 and [QiCiID]=9 group by UserId,UserName,UserDepartment union all select UserId,UserName, UserDepartment , 0 as zp, case when COUNT(*)>3 then ((SUM([KFHyzz])-MAX([KFHyzz])-MIN([KFHyzz])) +SUM([KFBb])-MAX([KFBb])-MIN([KFBb]) +(SUM([KFGdzc])-MAX([KFGdzc])-MIN([KFGdzc])) +(SUM([KFKf])-MAX([KFKf])-MIN([KFKf])) +(SUM([KFGztd])-MAX([KFGztd])-MIN([KFGztd])) +(SUM([KFFwys])-MAX([KFFwys])-MIN([KFFwys])) +(SUM([KFGtnl])-MAX([KFGtnl])-MIN([KFGtnl])) +(SUM([KFHbgz])-MAX([KFHbgz])-MIN([KFHbgz])) +(SUM([KFJlx])-MAX([KFJlx])-MIN([KFJlx])) +(SUM([KFXtnl])-MAX([KFXtnl])-MIN([KFXtnl])) )/(COUNT(*)-2 ) else sum([KFHyzz]+[KFBb]+[KFGdzc]+[KFKf]+[KFGztd]+[KFFwys]+[KFGtnl]+[KFHbgz]+[KFJlx]+[KFXtnl])/COUNT(*) end as hp , 0 as lp from dbo.ERPJiXiaoDaFenBiao where JiBie=2 and [QiCiID]=9 group by UserId,UserName,UserDepartment union all select UserId,UserName, UserDepartment , 0 as zp, case when COUNT(*)>3 then ((SUM([YFGzfh])-MAX([YFGzfh])-MIN([YFGzfh])) +SUM([YFNyd])-MAX([YFNyd])-MIN([YFNyd]) +(SUM([YFMbdc])-MAX([YFMbdc])-MIN([YFMbdc])) +(SUM([YFFgcs])-MAX([YFFgcs])-MIN([YFFgcs])) +(SUM([YFQxd])-MAX([YFQxd])-MIN([YFQxd])) +(SUM([YFGztd])-MAX([YFGztd])-MIN([YFGztd])) +(SUM([YFXtx])-MAX([YFXtx])-MIN([YFXtx])) +(SUM([YFJLX])-MAX([YFJLX])-MIN([YFJLX])) )/(COUNT(*)-2 ) else sum([YFGzfh]+[YFNyd]+[YFMbdc]+[YFFgcs]+[YFQxd]+[YFGztd]+[YFXtx]+[YFJLX])/COUNT(*) end as hp , 0 as lp from dbo.ERPJiXiaoDaFenBiao where JiBie=2 and [QiCiID]=9 group by UserId,UserName,UserDepartment union all select UserId,UserName, UserDepartment , 0 as zp, case when COUNT(*)>3 then ((SUM([ZJGzrw])-MAX([ZJGzrw])-MIN([ZJGzrw])) +SUM([ZJLdnl])-MAX([ZJLdnl])-MIN([ZJLdnl]) +(SUM([ZJChnl])-MAX([ZJChnl])-MIN([ZJChnl])) +(SUM([ZJSqzd])-MAX([ZJSqzd])-MIN([ZJSqzd])) +(SUM([ZJZztd])-MAX([ZJZztd])-MIN([ZJZztd])) +(SUM([ZJGtxt])-MAX([ZJGtxt])-MIN([ZJGtxt])) +(SUM([ZJCbys])-MAX([ZJCbys])-MIN([ZJCbys])) +(SUM([ZJJjwt])-MAX([ZJJjwt])-MIN([ZJJjwt])) )/(COUNT(*)-2 ) else sum([ZJGzrw]+[ZJLdnl]+[ZJChnl]+[ZJSqzd]+[ZJZztd]+[ZJGtxt]+[ZJCbys]+[ZJJjwt])/COUNT(*) end as hp , 0 as lp from dbo.ERPJiXiaoDaFenBiao where JiBie=2 and [QiCiID]=9 group by UserId,UserName,UserDepartment union all select UserId,UserName, UserDepartment , 0, 0, sum(ZongFen) as lp from ERPJiXiaoDaFenBiao where jibie=3 and [QiCiID]=9 group by UserId,UserName,UserDepartment

) a where UserName Like '%%' and UserDepartment Like '%%' group by a.UserId,a.UserName,a.UserDepartment