要求：绩效得分分为自评、平级评、上级评，其中自评占总分比例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