学步园

个人排名:

select a.onesumweight,a.wdonesumweight,a.zdonesumweight,a.operatorid,a.operator,a.compid,a.comp,<br /> row_number() over(partition by a.compid,a.comp order by a.onesumweight desc) as grpm<br /> ,a.totalcust,a.betweencust,a.newcust<br /> from<br /> (<br /> select sum(a.totalcust) as totalcust, sum(a.betweencust) as betweencust, sum(a.newcust) as newcust, sum(a.onesumweight) as onesumweight,sum(a.wdonesumweight) as wdonesumweight,sum(zdonesumweight) as zdonesumweight,a.operatorid,a.operator,a.compid,a.comp from<br /> (</p> <p> select 0 as totalcust, 0 as betweencust, 0 as newcust,sum(l.weight) as onesumweight,0 as wdonesumweight, 0 as zdonesumweight, m.operatorid,m.operator,m.compid,m.comp from es_contractline l<br /> join es_contract m on l.mainid=m.id and l.isdel=0 and m.isdel=0<br /> where 1=1<br /> group by m.operatorid,m.operator,m.compid,m.comp<br /> union all</p> <p> select 0 as totalcust, 0 as betweencust, 0 as newcust,0 as onesumweight, sum(l.weight) as wdonesumweight,0 as zdonesumweight, m.operatorid,m.operator,m.compid,m.comp from es_contractline l<br /> join es_contract m on l.mainid=m.id and l.isdel=0 and m.isdel=0<br /> where m.conproperty=18002 and 2=2 </p> <p> group by m.operatorid,m.operator,m.compid,m.comp<br /> union all</p> <p> select 0 as totalcust, 0 as betweencust, 0 as newcust, 0 as onesumweight, 0 as wdonesumweight, sum(l.weight) as zdonesumweight, m.operatorid,m.operator,m.compid,m.comp from es_contractline l<br /> join es_contract m on l.mainid=m.id and l.isdel=0 and m.isdel=0<br /> left join eb_corp c on m.corpid=c.id<br /> where c.isdel=0 and c.defaultcustsort=93002 and 3=3<br /> group by m.operatorid,m.operator,m.compid,m.comp</p> <p> union all</p> <p> select c.totalcust,c.betweencust,c.newcust, 0 as onesumweight, 0 as wdonesumweight,0 as zdonesumweight, c.operatorid,c.operator,c.compid,c.comp from<br /> (<br /> select sum(a.flag) as totalcust,0 as betweencust,0 as newcust, a.operatorid,a.operator,a.compid,a.comp from<br /> (<br /> select 1 as flag, a.operatorid,a.operator,a.compid,a.comp,a.corpid,a.corp from es_contract a<br /> left join eb_corp b on a.corpid=b.id<br /> where a.isdel=0 and b.isdel=0 and 6=6<br /> group by a.operatorid,a.operator,a.compid,a.comp,a.corpid,a.corp<br /> ) a group by a.operatorid,a.operator,a.compid,a.comp</p> <p> union all</p> <p> select 0 as totalcust,sum(a.flag) as betweencust,0 as newcust,a.operatorid,a.operator,a.compid,a.comp from<br /> (<br /> select 1 as flag, a.operatorid,a.operator,a.compid,a.comp,a.corpid,a.corp from es_contract a<br /> left join eb_corp b on a.corpid=b.id<br /> where a.isdel=0 and b.isdel=0 and 4=4<br /> group by a.operatorid,a.operator,a.compid,a.comp,a.corpid,a.corp<br /> ) a group by a.operatorid,a.operator,a.compid,a.comp</p> <p> union all</p> <p> select 0 as totalcust,0 as betweencust,sum(a.flag) as newcust, a.operatorid,a.operator,a.compid,a.comp from<br /> (<br /> select 1 as flag, a.operatorid,a.operator,a.compid,a.comp,a.corpid,a.corp from es_contract a<br /> left join eb_corp b on a.corpid=b.id<br /> where a.isdel=0 and b.isdel=0 and 5=5<br /> group by a.operatorid,a.operator,a.compid,a.comp,a.corpid,a.corp<br /> ) a group by a.operatorid,a.operator,a.compid,a.comp<br /> ) c</p> <p> ) a<br /> join eb_emp b on a.operatorid=b.id<br /> and b.groupname!='仓储配送部' and b.groupname!='物流部'<br /> where b.isdel=0<br /> group by a.operatorid,a.operator,a.compid,a.comp<br /> order by a.compid,sum(a.onesumweight) desc<br /> )a</p> <p><Field Name="onesumweight" Caption="个人销量"/><br /> <Field Name="wdonesumweight" Caption="个人临调"/><br /> <Field Name="zdonesumweight" Caption="个人终端"/><br /> <Field Name="operatorid" Caption="业务员ID"/><br /> <Field Name="operator" Caption="业务员"/><br /> <Field Name="compid" Caption="分公司ID"/><br /> <Field Name="comp" Caption="分公司"/><br /> <Field Name="grpm" Caption="排名"/><br /> <Field Name="totalcust" Caption="总操作客户数量"/><br /> <Field Name="betweencust" Caption="本期操作客户数量"/><br /> <Field Name="newcust" Caption="新开发客户数量"/>

团队排名:

select a.onesumweight, a.wdonesumweight,a.zdonesumweight, a.operatorid,a.operator,a.compid,a.comp,a.teamid,a.team,<br /> a.teamsumweight,a.teamwdsumweight,a.teamzdsumweight,round(a.teamzdsumweight/a.teamsumweight,3)*100||'%' as ZB,round(a.teamwdsumweight/a.teamsumweight,3)*100||'%' as LB,a.grpm,a.teampm<br /> from<br /> (<br /> select a3.onesumweight, a3.wdonesumweight,a3.zdonesumweight,a3.teamsumweight, a3.operatorid,a3.operator,a3.compid,a3.comp,a3.teamid,a3.team,<br /> sum(a3.wdonesumweight) over(partition by a3.teamid,a3.team order by a3.compid desc) as teamwdsumweight,<br /> sum(a3.zdonesumweight) over(partition by a3.teamid,a3.team order by a3.compid desc) as teamzdsumweight,</p> <p>row_number() over(partition by a3.compid,a3.comp,a3.teamid,a3.team order by a3.onesumweight desc) as grpm,<br /> a3.teampm<br /> from<br /> (<br /> select sum(a2.onesumweight) as onesumweight, sum(a2.wdonesumweight) as wdonesumweight,sum(a2.zdonesumweight) as zdonesumweight,sum(a2.teamsumweight) as teamsumweight,<br /> a2.operatorid ,a2.operator ,a2.compid,a2.comp,a2.teamid,a2.team ,b2.teampm<br /> from<br /> (</p> <p> select sum(a.onesumweight) as onesumweight, sum(a.wdonesumweight) as wdonesumweight,sum(a.zdonesumweight) as zdonesumweight,a.teamsumweight,<br /> a.operatorid,a.operator,a.compid,a.comp,a.teamid,a.team<br /> from(</p> <p> select 0 as onesumweight,0 as wdonesumweight, 0 as zdonesumweight,<br /> sum(l.weight) over(partition by z.teamid,z.team order by m.compid desc) as teamsumweight,<br /> m.operatorid,m.operator,m.compid,m.comp,z.teamid,z.team<br /> from es_contractline l<br /> join es_contract m on l.mainid=m.id and l.isdel=0 and m.isdel=0<br /> left join (<br /> select x.empid,y.id as teamid,y.teamname as team<br /> from eb_team_emp x<br /> inner join eb_team y<br /> on x.teamid=y.id<br /> where x.isdel=0 and y.isdel=0<br /> ) z<br /> on m.operatorid=z.empid<br /> where 1=1<br /> ) a group by a.operatorid,a.operator,a.compid,a.comp,a.teamid,a.team,a.teamsumweight</p> <p> union all</p> <p> select sum(a1.onesumweight) as onesumweight, sum(a1.wdonesumweight) as wdonesumweight,sum(a1.zdonesumweight) as zdonesumweight,sum(a1.teamsumweight) as teamsumweight,<br /> a1.operatorid,a1.operator,a1.compid,a1.comp,z.teamid,z.team<br /> from<br /> (<br /> --总销量<br /> select sum(l.weight) as onesumweight,0 as wdonesumweight, 0 as zdonesumweight, 0 as teamsumweight, m.operatorid,m.operator,m.compid,m.comp from es_contractline l<br /> join es_contract m on l.mainid=m.id and l.isdel=0 and m.isdel=0<br /> where 2=2<br /> group by m.operatorid,m.operator,m.compid,m.comp</p> <p> union all</p> <p> --外调销量<br /> select 0 as onesumweight, sum(l.weight) as wdonesumweight,0 as zdonesumweight, 0 as teamsumweight, m.operatorid,m.operator,m.compid,m.comp from es_contractline l<br /> join es_contract m on l.mainid=m.id and l.isdel=0 and m.isdel=0<br /> where m.conproperty=18002 and 3=3<br /> group by m.operatorid,m.operator,m.compid,m.comp</p> <p> union all<br /> --终端销量<br /> select 0 as onesumweight, 0 as wdonesumweight, sum(l.weight) as zdonesumweight,0 as teamsumweight, m.operatorid,m.operator,m.compid,m.comp from es_contractline l<br /> join es_contract m on l.mainid=m.id and l.isdel=0 and m.isdel=0<br /> left join eb_corp c on m.corpid=c.id<br /> where c.isdel=0 and c.defaultcustsort=93002 and 4=4<br /> group by m.operatorid,m.operator,m.compid,m.comp</p> <p> ) a1<br /> left join (<br /> select x.empid,y.id as teamid,y.teamname as team<br /> from eb_team_emp x<br /> inner join eb_team y<br /> on x.teamid=y.id<br /> where x.isdel=0 and y.isdel=0<br /> ) z<br /> on a1.operatorid=z.empid<br /> group by a1.operatorid,a1.operator,a1.compid,a1.comp,z.teamid,z.team </p> <p> ) a2<br /> left join<br /> (<br /> select row_number() over(partition by m.compid,m.comp order by sum(l.weight) desc) as teampm,<br /> z.teamid,z.team<br /> from es_contractline l<br /> join es_contract m on l.mainid=m.id and l.isdel=0 and m.isdel=0<br /> left join (<br /> select x.empid,y.id as teamid,y.teamname as team<br /> from eb_team_emp x<br /> inner join eb_team y<br /> on x.teamid=y.id<br /> where x.isdel=0 and y.isdel=0<br /> ) z<br /> on m.operatorid=z.empid<br /> where 5=5<br /> group by z.teamid,z.team ,m.compid,m.comp<br /> )b2 on a2.teamid=b2.teamid</p> <p>group by a2.compid,a2.comp,a2.teamid,a2.team,a2.operatorid,a2.operator ,b2.teampm</p> <p>)a3 </p> <p>)a </p> <p>join eb_emp b on a.operatorid=b.id<br /> where b.isdel=0 and 6=6<br /> order by a.compid,a.teampm,a.grpm</p> <p><Field Name="onesumweight" Caption="个人销量"/><br /> <Field Name="wdonesumweight" Caption="个人临调"/><br /> <Field Name="zdonesumweight" Caption="个人终端"/><br /> <Field Name="operatorid" Caption="业务员ID"/><br /> <Field Name="operator" Caption="业务员"/><br /> <Field Name="compid" Caption="分公司ID"/><br /> <Field Name="comp" Caption="分公司"/><br /> <Field Name="teamid" Caption="团队ID"/><br /> <Field Name="team" Caption="团队"/><br /> <Field Name="teamsumweight" Caption="团队销量"/><br /> <Field Name="teamwdsumweight" Caption="团队临调"/><br /> <Field Name="teamzdsumweight" Caption="团队终端"/><br /> <Field Name="ZB" Caption="终比"/><br /> <Field Name="LB" Caption="临比"/><br /> <Field Name="grpm" Caption="个人排名"/><br /> <Field Name="teampm" Caption="团队排名"/>