mysql数据库WITH t1 as (SELECT ttu_mobile,ttu_id,ttu_name,ttu_role,TTU_BUSINESS_TYPE,TTU_STATUS,REPLACE ( ttu_name,"'","") as xsdb,-- 销售代表,REPLACE ( TTU_SUPERIOR_NAME,"'","") as dbld -- 代表领导 FROM total_user WHERE TTU_ROLE = 'SE' AND ttu_name IS NOT NULL AND TTU_STATUS = '1' ),t2 as (SELECT REPLACE( ttu_name,"'","") as xsjl,-- 销售经理,REPLACE ( TTU_SUPERIOR_NAME, "'","") as jlld -- 经理领导 FROM total_user WHERE TTU_ROLE = 'ASM' AND ttu_name IS NOT NULL AND TTU_STATUS = '1' GROUP BY ttu_name,TTU_SUPERIOR_NAME ),t3 as (SELECT REPLACE(ttu_name,"'","") as xstd, -- 销售团队,REPLACE (TTU_SUPERIOR_NAME,"'","") as tdld -- 团队领导 FROM total_user WHERE TTU_ROLE = 'RSD' AND ttu_name IS NOT NULL AND TTU_STATUS = '1' GROUP BY ttu_name,TTU_SUPERIOR_NAME),t4 as (select t1.ttu_mobile,t1.ttu_id,t1.ttu_name,t1.ttu_role,t1.TTU_BUSINESS_TYPE,t1.TTU_STATUS,t3.xstd,t2.xsjl,t1.xsdbfrom t1 left join t2 on t1.dbld = t2.xsjlleft join t3 on t2.jlld = t3.xstdwhere t3.xstd is not null),t5 as (select dealer_code,sum(ifnull(target_cust_num,0))as target_cust_numfrom dm_dbp_cusnum_year_proline_custypewhere 1=1${if(ROUNDUP(MONTH(sdate)/3,0)!=ROUNDUP(MONTH(edate)/3,0),"and 1=0",if(RIGHT(sdate,2)=="01","and quarter = CONCAT(left('"+sdate+"',4),'-','1')",if(RIGHT(sdate,2)=="02","and quarter = CONCAT(left('"+sdate+"',4),'-','1')",if(RIGHT(sdate,2)=="03","and quarter = CONCAT(left('"+sdate+"',4),'-','1')",if(RIGHT(sdate,2)=="04","and quarter = CONCAT(left('"+sdate+"',4),'-','2')",if(RIGHT(sdate,2)=="05","and quarter = CONCAT(left('"+sdate+"',4),'-','2')",if(RIGHT(sdate,2)=="06","and quarter = CONCAT(left('"+sdate+"',4),'-','2')",if(RIGHT(sdate,2)=="07","and quarter = CONCAT(left('"+sdate+"',4),'-','3')",if(RIGHT(sdate,2)=="08","and quarter = CONCAT(left('"+sdate+"',4),'-','3')",if(RIGHT(sdate,2)=="09","and quarter = CONCAT(left('"+sdate+"',4),'-','3')",if(RIGHT(sdate,2)=="10","and quarter = CONCAT(left('"+sdate+"',4),'-','4')",if(RIGHT(sdate,2)=="11","and quarter = CONCAT(left('"+sdate+"',4),'-','4')","and quarter = CONCAT(left('"+sdate+"',4),'-','4')"))))))))))))}group by dealer_code),t6 as (select dealer_code,sum(ifnull(cust_num,0))as cust_numfrom dm_act_cusnum_year_proline_custypewhere 1=1${if(ROUNDUP(MONTH(sdate)/3,0)!=ROUNDUP(MONTH(edate)/3,0),"and 1=0",if(RIGHT(sdate,2)=="01","and quarter = CONCAT(left('"+sdate+"',4),'-','1')",if(RIGHT(sdate,2)=="02","and quarter = CONCAT(left('"+sdate+"',4),'-','1')",if(RIGHT(sdate,2)=="03","and quarter = CONCAT(left('"+sdate+"',4),'-','1')",if(RIGHT(sdate,2)=="04","and quarter = CONCAT(left('"+sdate+"',4),'-','2')",if(RIGHT(sdate,2)=="05","and quarter = CONCAT(left('"+sdate+"',4),'-','2')",if(RIGHT(sdate,2)=="06","and quarter = CONCAT(left('"+sdate+"',4),'-','2')",if(RIGHT(sdate,2)=="07","and quarter = CONCAT(left('"+sdate+"',4),'-','3')",if(RIGHT(sdate,2)=="08","and quarter = CONCAT(left('"+sdate+"',4),'-','3')",if(RIGHT(sdate,2)=="09","and quarter = CONCAT(left('"+sdate+"',4),'-','3')",if(RIGHT(sdate,2)=="10","and quarter = CONCAT(left('"+sdate+"',4),'-','4')",if(RIGHT(sdate,2)=="11","and quarter = CONCAT(left('"+sdate+"',4),'-','4')","and quarter = CONCAT(left('"+sdate+"',4),'-','4')"))))))))))))}group by dealer_code),t7 as (SELECT dealer_code,sum(prj_index_value) PVALUEFROM dm_dbp_mkt_supportWHERE 1=1${if(ROUNDUP(MONTH(sdate)/3,0)!=ROUNDUP(MONTH(edate)/3,0),"and 1=0",if(RIGHT(sdate,2)=="01","and ym = CONCAT(left('"+sdate+"',4),'-','1')",if(RIGHT(sdate,2)=="02","and ym = CONCAT(left('"+sdate+"',4),'-','1')",if(RIGHT(sdate,2)=="03","and ym = CONCAT(left('"+sdate+"',4),'-','1')",if(RIGHT(sdate,2)=="04","and ym = CONCAT(left('"+sdate+"',4),'-','2')",if(RIGHT(sdate,2)=="05","and ym = CONCAT(left('"+sdate+"',4),'-','2')",if(RIGHT(sdate,2)=="06","and ym = CONCAT(left('"+sdate+"',4),'-','2')",if(RIGHT(sdate,2)=="07","and ym = CONCAT(left('"+sdate+"',4),'-','3')",if(RIGHT(sdate,2)=="08","and ym = CONCAT(left('"+sdate+"',4),'-','3')",if(RIGHT(sdate,2)=="09","and ym = CONCAT(left('"+sdate+"',4),'-','3')",if(RIGHT(sdate,2)=="10","and ym = CONCAT(left('"+sdate+"',4),'-','4')",if(RIGHT(sdate,2)=="11","and ym = CONCAT(left('"+sdate+"',4),'-','4')","and ym = CONCAT(left('"+sdate+"',4),'-','4')"))))))))))))}GROUP BY dealer_code),t8 as (SELECT dealer_code,sum(prj_index_value) APVALUEFROM dm_act_year_mkt_supportWHERE 1=1${if(ROUNDUP(MONTH(sdate)/3,0)!=ROUNDUP(MONTH(edate)/3,0),"and 1=0",if(RIGHT(sdate,2)=="01","and quarter = CONCAT(left('"+sdate+"',4),'-','1')",if(RIGHT(sdate,2)=="02","and quarter = CONCAT(left('"+sdate+"',4),'-','1')",if(RIGHT(sdate,2)=="03","and quarter = CONCAT(left('"+sdate+"',4),'-','1')",if(RIGHT(sdate,2)=="04","and quarter = CONCAT(left('"+sdate+"',4),'-','2')",if(RIGHT(sdate,2)=="05","and quarter = CONCAT(left('"+sdate+"',4),'-','2')",if(RIGHT(sdate,2)=="06","and quarter = CONCAT(left('"+sdate+"',4),'-','2')",if(RIGHT(sdate,2)=="07","and quarter = CONCAT(left('"+sdate+"',4),'-','3')",if(RIGHT(sdate,2)=="08","and quarter = CONCAT(left('"+sdate+"',4),'-','3')",if(RIGHT(sdate,2)=="09","and quarter = CONCAT(left('"+sdate+"',4),'-','3')",if(RIGHT(sdate,2)=="10","and quarter = CONCAT(left('"+sdate+"',4),'-','4')",if(RIGHT(sdate,2)=="11","and quarter = CONCAT(left('"+sdate+"',4),'-','4')","and quarter = CONCAT(left('"+sdate+"',4),'-','4')"))))))))))))}GROUP BY dealer_code),t9 as (SELECT dealer_code,sum(prj_index_value) LPVALUEFROM dm_act_year_mkt_supportWHERE 1=1${if(ROUNDUP(MONTH(sdate)/3,0)!=ROUNDUP(MONTH(edate)/3,0),"and 1=0",if(RIGHT(sdate,2)=="01","and quarter = CONCAT(left('"+sdate+"',4),'-','1')",if(RIGHT(sdate,2)=="02","and quarter = CONCAT(left('"+sdate+"',4),'-','1')",if(RIGHT(sdate,2)=="03","and quarter = CONCAT(left('"+sdate+"',4),'-','1')",if(RIGHT(sdate,2)=="04","and quarter = CONCAT(left('"+sdate+"',4),'-','2')",if(RIGHT(sdate,2)=="05","and quarter = CONCAT(left('"+sdate+"',4),'-','2')",if(RIGHT(sdate,2)=="06","and quarter = CONCAT(left('"+sdate+"',4),'-','2')",if(RIGHT(sdate,2)=="07","and quarter = CONCAT(left('"+sdate+"',4),'-','3')",if(RIGHT(sdate,2)=="08","and quarter = CONCAT(left('"+sdate+"',4),'-','3')",if(RIGHT(sdate,2)=="09","and quarter = CONCAT(left('"+sdate+"',4),'-','3')",if(RIGHT(sdate,2)=="10","and quarter = CONCAT(left('"+sdate+"',4),'-','4')",if(RIGHT(sdate,2)=="11","and quarter = CONCAT(left('"+sdate+"',4),'-','4')","and quarter = CONCAT(left('"+sdate+"',4),'-','4')"))))))))))))}GROUP BY dealer_code)select sum(b.target_sell_in_volumn) as 采购目标from (select a.xstd,a.xsjl,a.xsdb,a.dtb_code,a.dtb_cname,sum(ifnull(a.target_sell_in_volumn,0))as target_sell_in_volumnfrom(select t4.*,d.dtb_code,d.dtb_cname,ddsy.year,ddsy.pro_series_code,ddsy.pro_line_name,ddsy.target_sell_in_volumn,dp.pro_product_labelingfrom t4 left join (select distinct dtb_code,DTB_CNAME,DTB_CREATE_USER from distributor where DTB_STATUS = 1) d on t4.ttu_mobile = d.DTB_CREATE_USERleft join dm_dbp_sellin_year_proseries ddsy on d.dtb_code = ddsy.dealer_code and ddsy.year = left('${sdate}',4)left join (select distinct d.pro_series_code ,d.pro_line_name,pro_product_labeling from dim_product d where d.pro_series_code is not null and d.pro_line_name is not null) dpon dp.pro_series_code = ddsy.pro_series_code)a where 1=1${if(len(product)=0,"","and pro_line_name in('"+product+"')")}${if(len(pro_product_labeling)=0,"","and pro_product_labeling in('"+pro_product_labeling+"')")}group by a.xstd,a.xsjl,a.xsdb,a.dtb_code,a.dtb_cname)b left join t5 on b.dtb_code = t5.dealer_codeleft join t6 on b.dtb_code = t6.dealer_codeleft join t7 on b.dtb_code = t7.dealer_codeleft join t8 on b.dtb_code = t8.dealer_codeleft join t9 on b.dtb_code = t9.dealer_codewhere 1=1${if(len(XSTD)=0,"","and xstd in('"+XSTD+"')")}${if(len(XSJL)=0,"","and xsjl in('"+XSJL+"')")}${if(len(XSDB)=0,"","and xsdb in('"+XSDB+"')")}${if(len(JXS)=0,"","and dtb_code in('"+JXS+"')")}
