CRM客户汇总数据sql

//联系人信息    wk_crm_contacts  
   //客户信息      wk_crm_customer
   //用户信息表    wk_admin_user
   //用户部门信息  wk_admin_dept
#新客户累计总数 1
select COUNT(customer_id) as sum_quantity,owner_user_id,create_time,update_time from wk_crm_customer  GROUP BY owner_user_id

#累计成交客户数 2
select COUNT(customer_id) as finish_quantity,owner_user_id,create_time,update_time from wk_crm_customer where deal_status='1'   GROUP BY owner_user_id

#跟进中客户 3
select COUNT(customer_id) as follow_quantity,owner_user_id,create_time,update_time from wk_crm_customer where deal_status='0'   GROUP BY owner_user_id

#终止结束客户 4

select COUNT(customer_id) as over_quantity,owner_user_id,create_time,update_time from wk_crm_customer where deal_status='3'   GROUP BY owner_user_id

#当月新增客户数 5
select COUNT(customer_id) as now_add_quantity,owner_user_id, date_format(create_time ,'%Y-%m') as create_time, date_format(update_time ,'%Y-%m') as update_time from wk_crm_customer  
  GROUP BY date_format(create_time ,'%Y-%m')
	
	
	#当月新增成交客户数量 6
select COUNT(customer_id) as now_finish_quantity,owner_user_id,date_format(create_time ,'%Y-%m') as create_time, date_format(update_time ,'%Y-%m') as update_time from wk_crm_customer
    where deal_status='1' 
    GROUP BY date_format(update_time ,'%Y-%m')
		
		
		#当月终止客户数	 7
select COUNT(customer_id) as now_finish_quantity,owner_user_id,date_format(create_time ,'%Y-%m') as create_time, date_format(update_time ,'%Y-%m') as update_time from wk_crm_customer
    where deal_status='3' 
    GROUP BY date_format(update_time ,'%Y-%m')
	#人员部门信息	
 select a.realname,b.name as dept_name,a.user_id from wk_admin_user a left join wk_admin_dept b on a.dept_id =b.dept_id
	
   
   
  #月份汇总
	
	select a.create_time,a.owner_user_id,a.now_add_quantity,b.now_finish_quantity,now_over_quantity from 
	(select COUNT(customer_id) as now_add_quantity,owner_user_id, date_format(create_time ,'%Y-%m') as create_time, date_format(update_time ,'%Y-%m') as update_time from wk_crm_customer 
  GROUP BY owner_user_id,date_format(create_time ,'%Y-%m')) a  
	  
		left join (
			select COUNT(customer_id) as now_finish_quantity,owner_user_id,date_format(create_time ,'%Y-%m') as create_time, date_format(update_time ,'%Y-%m') as update_time,deal_status
			from wk_crm_customer
			where deal_status='1' 
			GROUP BY owner_user_id, date_format(update_time ,'%Y-%m')
		) b on a.owner_user_id=b.owner_user_id and a.create_time=b.update_time
		
		left join (
		   select COUNT(customer_id) as now_over_quantity,owner_user_id,date_format(create_time ,'%Y-%m') as create_time, date_format(update_time ,'%Y-%m') as update_time,deal_status 
      from wk_crm_customer
			where deal_status='3'
			GROUP BY owner_user_id, date_format(update_time ,'%Y-%m') 
		) c on a.owner_user_id=c.owner_user_id and a.create_time=c.update_time
		
		
		
		
		#客户汇总
	select n.dept_name,n.realname,m.owner_user_id,m.sum_quantity,m.finish_quantity,m.follow_quantity,m.over_quantity from  ( select a.owner_user_id,a.sum_quantity,b.finish_quantity,c.follow_quantity,d.                     over_quantity from 

											(
												 select COUNT(customer_id) as sum_quantity,owner_user_id,create_time,update_time from wk_crm_customer  GROUP BY owner_user_id
											) a 
											left join (
														 select COUNT(customer_id) as finish_quantity,owner_user_id,create_time,update_time from wk_crm_customer where deal_status='1'   GROUP BY owner_user_id
											 ) b on a.owner_user_id=b.owner_user_id
											 
											 left join (
													 select COUNT(customer_id) as follow_quantity,owner_user_id,create_time,update_time from wk_crm_customer where deal_status='0'   GROUP BY owner_user_id
											 ) c on a.owner_user_id=c.owner_user_id
											 
											 left join (
													select COUNT(customer_id) as over_quantity,owner_user_id,create_time,update_time from wk_crm_customer where deal_status='3'   GROUP BY owner_user_id
											 ) d on a.owner_user_id=d.owner_user_id
	                 ) m
	 left join (
	         select a.realname,b.name as dept_name,a.user_id from wk_admin_user a left join wk_admin_dept b on a.dept_id =b.dept_id  
	 ) n on m.owner_user_id=n.user_id


 
 #人员部门信息
 
 select a.realname,b.name as dept_name,a.user_id from wk_admin_user a left join wk_admin_dept b on a.dept_id =b.dept_id

　　最新汇总

#月份汇总
     
    select a.create_time,a.owner_user_id,a.now_add_quantity,b.now_finish_quantity,now_over_quantity from
    (select COUNT(customer_id) as now_add_quantity,owner_user_id, date_format(create_time ,'%Y-%m') as create_time, date_format(update_time ,'%Y-%m') as update_time from wk_crm_customer
  GROUP BY owner_user_id,date_format(create_time ,'%Y-%m')) a 
       
        left join (
            select COUNT(customer_id) as now_finish_quantity,owner_user_id,date_format(create_time ,'%Y-%m') as create_time, date_format(update_time ,'%Y-%m') as update_time,deal_status
            from wk_crm_customer
            where deal_status='1'
            GROUP BY owner_user_id, date_format(update_time ,'%Y-%m')
        ) b on a.owner_user_id=b.owner_user_id and a.create_time=b.update_time
         
        left join (
           select COUNT(customer_id) as now_over_quantity,owner_user_id,date_format(create_time ,'%Y-%m') as create_time, date_format(update_time ,'%Y-%m') as update_time,deal_status
      from wk_crm_customer
            where deal_status='3'
            GROUP BY owner_user_id, date_format(update_time ,'%Y-%m')
        ) c on a.owner_user_id=c.owner_user_id and a.create_time=c.update_time
         
         
         
         
        #客户汇总
    select n.dept_name,n.realname,m.owner_user_id,m.sum_quantity,m.finish_quantity,m.follow_quantity,m.over_quantity from  ( select a.owner_user_id,a.sum_quantity,b.finish_quantity,c.follow_quantity,d.                     over_quantity from
 
                                            (
                                                 select COUNT(customer_id) as sum_quantity,owner_user_id,create_time,update_time from wk_crm_customer  GROUP BY owner_user_id
                                            ) a
                                            left join (
                                                         select COUNT(customer_id) as finish_quantity,owner_user_id,create_time,update_time from wk_crm_customer where deal_status='1'   GROUP BY owner_user_id
                                             ) b on a.owner_user_id=b.owner_user_id
                                              
                                             left join (
                                                     select COUNT(customer_id) as follow_quantity,owner_user_id,create_time,update_time from wk_crm_customer where deal_status='0'   GROUP BY owner_user_id
                                             ) c on a.owner_user_id=c.owner_user_id
                                              
                                             left join (
                                                    select COUNT(customer_id) as over_quantity,owner_user_id,create_time,update_time from wk_crm_customer where deal_status='3'   GROUP BY owner_user_id
                                             ) d on a.owner_user_id=d.owner_user_id where a.owner_user_id is not null
                     ) m
     left join (
             select a.realname,b.name as dept_name,a.user_id from wk_admin_user a left join wk_admin_dept b on a.dept_id =b.dept_id 
     ) n on m.owner_user_id=n.user_id
	 
	 
	 //sql报表
	 select a.dept_name as '部门',a.realname as '负责人',a.sum_quantity as '累计新增客户',a.finish_quantity as '累计成交客户',follow_quantity as '累计跟进客户',a.over_quantity as '累计终止客户',
   b.now_add_quantity as '当月新增客户',b.now_finish_quantity as '当月成交客户',b.now_over_quantity as '当月终止客户'
from user_customer_all a left JOIN
(select * from user_customer_month
            where create_time='2021-03')b on a.owner_user_id=b.owner_user_id
			
			
			
//添加了一条数据
	select '部门','负责人','累计新增客户','累计成交客户','累计跟进客户','累计终止客户','当月新增客户','当月成交客户','当月终止客户' from (select a.dept_name as '部门',a.realname as '负责人',a.sum_quantity as '累计新增客户',a.finish_quantity as '累计成交客户',follow_quantity as '累计跟进客户',a.over_quantity as '累计终止客户',
   b.now_add_quantity as '当月新增客户',b.now_finish_quantity as '当月成交客户',b.now_over_quantity as '当月终止客户'
from user_customer_all a left JOIN
(select * from user_customer_month
            where create_time='2021-03')b on a.owner_user_id=b.owner_user_id) mm 

union 


select a.dept_name as '部门',a.realname as '负责人',a.sum_quantity as '累计新增客户',a.finish_quantity as '累计成交客户',follow_quantity as '累计跟进客户',a.over_quantity as '累计终止客户',
   b.now_add_quantity as '当月新增客户',b.now_finish_quantity as '当月成交客户',b.now_over_quantity as '当月终止客户'
from user_customer_all a left JOIN
(select * from user_customer_month
            where create_time='2021-03')b on a.owner_user_id=b.owner_user_id