ORACLE个人排名,团队排名报表

个人排名:

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

    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
    join es_contract m on l.mainid=m.id and l.isdel=0 and m.isdel=0
    where 1=1
    group by m.operatorid,m.operator,m.compid,m.comp
    union all

    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
    join es_contract m on l.mainid=m.id and l.isdel=0 and m.isdel=0
    where m.conproperty=18002 and 2=2
   
    group by m.operatorid,m.operator,m.compid,m.comp
    union all

    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
    join es_contract m on l.mainid=m.id and l.isdel=0 and m.isdel=0
    left join eb_corp c on m.corpid=c.id
    where c.isdel=0 and c.defaultcustsort=93002 and 3=3
    group by m.operatorid,m.operator,m.compid,m.comp

    union all
      
    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
    (
              select sum(a.flag) as totalcust,0 as betweencust,0 as newcust, a.operatorid,a.operator,a.compid,a.comp from
              (
                  select 1 as flag, a.operatorid,a.operator,a.compid,a.comp,a.corpid,a.corp from es_contract a
                  left join eb_corp b on a.corpid=b.id
                  where  a.isdel=0 and b.isdel=0 and 6=6
                  group by a.operatorid,a.operator,a.compid,a.comp,a.corpid,a.corp         
              ) a group by a.operatorid,a.operator,a.compid,a.comp
                  
          union all
         
              select  0 as totalcust,sum(a.flag) as betweencust,0 as newcust,a.operatorid,a.operator,a.compid,a.comp from
              (
                  select 1 as flag, a.operatorid,a.operator,a.compid,a.comp,a.corpid,a.corp from es_contract a
                  left join eb_corp b on a.corpid=b.id
                  where a.isdel=0 and b.isdel=0 and 4=4            
                  group by a.operatorid,a.operator,a.compid,a.comp,a.corpid,a.corp            
              ) a group by a.operatorid,a.operator,a.compid,a.comp
                  
          union all
         
              select 0 as totalcust,0 as betweencust,sum(a.flag) as newcust, a.operatorid,a.operator,a.compid,a.comp from
              (
                  select 1 as flag, a.operatorid,a.operator,a.compid,a.comp,a.corpid,a.corp from es_contract a
                  left join eb_corp b on a.corpid=b.id
                  where  a.isdel=0 and b.isdel=0 and 5=5
                  group by a.operatorid,a.operator,a.compid,a.comp,a.corpid,a.corp           
              ) a group by a.operatorid,a.operator,a.compid,a.comp
       ) c

    ) a
    join eb_emp b on a.operatorid=b.id
    and b.groupname!='仓储配送部' and b.groupname!='物流部'
    where  b.isdel=0 
    group by a.operatorid,a.operator,a.compid,a.comp
    order by a.compid,sum(a.onesumweight) desc
)a

 

<Field Name="onesumweight" Caption="个人销量"/>
<Field Name="wdonesumweight" Caption="个人临调"/>
<Field Name="zdonesumweight" Caption="个人终端"/>
<Field Name="operatorid" Caption="业务员ID"/>
<Field Name="operator" Caption="业务员"/>
<Field Name="compid" Caption="分公司ID"/>
<Field Name="comp" Caption="分公司"/>
<Field Name="grpm" Caption="排名"/>
<Field Name="totalcust" Caption="总操作客户数量"/>
<Field Name="betweencust" Caption="本期操作客户数量"/>
<Field Name="newcust" Caption="新开发客户数量"/>

团队排名:

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

 

本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/xiao_jun_0820/archive/2010/04/02/5445666.aspx