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
