select aaaa.customer_name,CUSTOMER_CODE, ar '销售额/月', arp '毛利/月',arpv '毛利率/月', weightCoefficient '分数', ifnull(grade, '不重要') '客户价值分级',

case when @temp_weight= weightCoefficient then @rank2 
             when @temp_weight:=weightCoefficient then @rank2:=@index2
             else @rank2:=@index2 END '排名',
             (@index2:=@index2+1)-1 '序号'

from (

        select a1.customer_name,a1.CUSTOMER_CODE, sales, ifnull(a2.cnCount,0) ar, ifnull(a3.cnCount,0) arp, ifnull(a4.cnCount,0) arpv,
         ifnull(a2.weightCoefficient,0) + ifnull(a3.weightCoefficient,0)+ ifnull(a4.weightCoefficient,0) weightCoefficient,
         (select grade from scp_erp_accounts_score_grade
                    where (ifnull(a2.weightCoefficient,0) + ifnull(a3.weightCoefficient,0)+ ifnull(a4.weightCoefficient,0))>SCORE_Min
                       and ( ifnull(a2.weightCoefficient,0) + ifnull(a3.weightCoefficient,0)+ ifnull(a4.weightCoefficient,0))<= SCORE_MAX) grade
     from
        (   
         select CUSTOMER_CODE, CUSTOMER_NAME, GROUP_CONCAT(distinct sales) sales  from  srp_erp_business_info 
                where create_date between DATE_SUB(DATE_SUB(CURDATE(), INTERVAL DAY(CURDATE())-1 DAY), INTERVAL 6 MONTH) and LAST_DAY(now() - INTERVAL 1 MONTH)  
            group by CUSTOMER_CODE, CUSTOMER_NAME 
    ) a1
     left join (
            select aa.CUSTOMER_CODE, customer_name, cnCount, rank,
                    aa.rateScore*(select weight from scp_erp_quote_weight_coefficient bbb where quote_type='zh' and quote_name = 'revenue' limit 1) weightCoefficient
             from ( 
                    select CUSTOMER_CODE, customer_name, cnCount , allCount, rank, ic,
                                rank*100 div allCount pm, 
                            case when cnCount =0 then 10
                                    when rank*100 div allCount=0 then 100
                                 else (select score from scp_erp_quote_rate_score where (rank*100 div allCount) >RATE_MIN and (rank*100 div allCount)<=RATE_MAX ) end rateScore  
                 from (
                        select  CUSTOMER_CODE, customer_name, cnCount div 6 cnCount,  
                                case when @temp_cnCount= cnCount then @rank 
                                         when @temp_cnCount:=cnCount then @rank:=@index1
                                         else @rank:=@index1 END rank,
                                         (@index1:=@index1+1)-1 ic
                     from  (
                             select CUSTOMER_CODE, customer_name, sum(ifnull(ar_amount_rmb,0))/6 cnCount  
                             from srp_erp_business_info
                                where create_date between DATE_SUB(DATE_SUB(CURDATE(), INTERVAL DAY(CURDATE())-1 DAY), INTERVAL 6 MONTH) and LAST_DAY(now() - INTERVAL 1 MONTH)    
                             group by CUSTOMER_CODE, customer_name  
                        ) s, 
                        (select @index1:=1,@rank:=0,@temp_cnCount:=null) temp 
                        ORDER BY cnCount DESC
                 ) aa, 
                        (   
                         select count(1) allCount from (
                             select CUSTOMER_CODE, customer_name from srp_erp_business_info 
                            where create_date between DATE_SUB(DATE_SUB(CURDATE(), INTERVAL DAY(CURDATE())-1 DAY), INTERVAL 6 MONTH) and LAST_DAY(now() - INTERVAL 1 MONTH)  
                            group by CUSTOMER_CODE, customer_name 
                         ) a
                     )b
                ) aa
     )a2 on a1.CUSTOMER_CODE=a2.CUSTOMER_CODE and a1.customer_name=a2.customer_name 
     left join (
            select aa.CUSTOMER_CODE, customer_name, cnCount, rank,
                    aa.rateScore*(select weight from scp_erp_quote_weight_coefficient bbb where quote_type='zh' and quote_name = 'grossProfit' limit 1) weightCoefficient
             from ( 
                    select CUSTOMER_CODE, customer_name, cnCount , allCount, rank, ic,
                                rank*100 div allCount pm, 
                            case when cnCount =0 then 10
                                    when rank*100 div allCount=0 then 100
                                 else (select score from scp_erp_quote_rate_score where (rank*100 div allCount) >RATE_MIN and (rank*100 div allCount)<=RATE_MAX ) end rateScore  
                 from (
                        select  CUSTOMER_CODE, customer_name, cnCount div 6 cnCount,  
                                case when @temp_cnCount= cnCount then @rank 
                                         when @temp_cnCount:=cnCount then @rank:=@index1
                                         else @rank:=@index1 END rank,
                                         (@index1:=@index1+1)-1 ic
                     from  (
                             select CUSTOMER_CODE, customer_name, sum(ifnull(ar_amount_rmb,0)-ifnull(ap_amount_rmb,0))/6 cnCount  
                             from srp_erp_business_info
                                where create_date between DATE_SUB(DATE_SUB(CURDATE(), INTERVAL DAY(CURDATE())-1 DAY), INTERVAL 6 MONTH) and LAST_DAY(now() - INTERVAL 1 MONTH)    
                             group by CUSTOMER_CODE, customer_name  
                        ) s, 
                        (select @index1:=1,@rank:=0,@temp_cnCount:=null) temp 
                        ORDER BY cnCount DESC
                 ) aa, 
                        (   
                         select count(1) allCount from (
                             select CUSTOMER_CODE, customer_name from srp_erp_business_info 
                            where create_date between DATE_SUB(DATE_SUB(CURDATE(), INTERVAL DAY(CURDATE())-1 DAY), INTERVAL 6 MONTH) and LAST_DAY(now() - INTERVAL 1 MONTH)  
                            group by CUSTOMER_CODE, customer_name 
                         ) a
                     )b
                ) aa
     )a3  on a1.CUSTOMER_CODE=a3.CUSTOMER_CODE and a1.customer_name=a3.customer_name 
     left join  (
            select aa.CUSTOMER_CODE, customer_name, cnCount, rank,
                    aa.rateScore*(select weight from scp_erp_quote_weight_coefficient bbb where quote_type='zh' and quote_name = 'profitRate' limit 1) weightCoefficient
             from ( 
                    select CUSTOMER_CODE, customer_name, cnCount , allCount, rank, ic,
                                rank*100 div allCount pm, 
                            case when cnCount =0 then 10
                                    when rank*100 div allCount=0 then 100
                                    else (select score from scp_erp_quote_rate_score where (rank*100 div allCount) >RATE_MIN and (rank*100 div allCount)<=RATE_MAX ) end rateScore  
                 from (
                        select  CUSTOMER_CODE, customer_name, cnCount div 6 cnCount,  
                                case when @temp_cnCount= cnCount then @rank 
                                         when @temp_cnCount:=cnCount then @rank:=@index1
                                         else @rank:=@index1 END rank,
                                         (@index1:=@index1+1)-1 ic
                     from  (
                             select CUSTOMER_CODE, customer_name,
                             case when sum(ifnull(ar_amount_rmb,0))>0 then (sum(ifnull(ar_amount_rmb,0)-ifnull(ap_amount_rmb,0)) div sum(ifnull(ar_amount_rmb,0))) div 6
                                         else 0 end cnCount  
                             from srp_erp_business_info
                                where create_date between DATE_SUB(DATE_SUB(CURDATE(), INTERVAL DAY(CURDATE())-1 DAY), INTERVAL 6 MONTH) and LAST_DAY(now() - INTERVAL 1 MONTH)    
                             group by CUSTOMER_CODE, customer_name  
                        ) s, 
                        (select @index1:=1,@rank:=0,@temp_cnCount:=null) temp 
                        ORDER BY cnCount DESC
                 ) aa, 
                        (   
                         select count(1) allCount from   from srp_erp_business_info 
                            where create_date between DATE_SUB(DATE_SUB(CURDATE(), INTERVAL DAY(CURDATE())-1 DAY), INTERVAL 6 MONTH) and LAST_DAY(now() - INTERVAL 1 MONTH)  
                            group by CUSTOMER_CODE, customer_name 
                         ) a
                     )b
                ) aa
     )a4  on a1.CUSTOMER_CODE=a4.CUSTOMER_CODE and a1.customer_name=a4.customer_name 

)aaaa, (select @index2:=1,@rank2:=0,@temp_weight:=null) temp order by weightCoefficient, CUSTOMER_CODE, customer_name