要求：績效得分分為自評、平級評、上級評，其中自評占總分比例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

轉載于:https://www.cnblogs.com/lu-lin/p/3757244.html

總結

以上是生活随笔為你收集整理的获取绩效统计列表的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。