销售提成计算

标品类下单客户统计

select distinct o.b_user_id,SUM(p.prod_num*p.prod_price) as sum  from TP_ORDER o INNER JOIN 

(select d.order_id as order_id,d.prod_price,d.prod_num from TP_ORDER_PROD d INNER JOIN TP_PROD pd on d.prod_id = pd.id 

where pd.fcategory_id in (1988,4) )

 p on o.order_id = p.order_id 

INNER JOIN (select c.user_id,c.marketer_id,m.login_name,m.display_name,c.buyer_category

from TP_CRM_CUSTOMER c

INNER JOIN TP_USER m on c.marketer_id = m.user_id where c.del=0 and c.user_type = 1 and c.marketer_id = 40)

cc on o.b_user_id = cc.user_id

where o.order_dt between str_to_date('2015-07-01','%Y-%m-%d %H:%i:%s') and

   str_to_date('2015-08-01','%Y-%m-%d %H:%i:%s') and o.`status` not in (0,6,7,8) 

GROUP BY o.b_user_id  order by sum desc

标品类新增客户统计

select distinct o.b_user_id,SUM(p.prod_num*p.prod_price) as sum  from TP_ORDER o 

INNER JOIN TP_BUYER b on o.b_user_id = b.user_id 

INNER JOIN 

(select d.order_id as order_id,d.prod_price,d.prod_num from TP_ORDER_PROD d INNER JOIN TP_PROD pd on d.prod_id = pd.id 

where pd.fcategory_id in (1988,4) )

 p on o.order_id = p.order_id 

INNER JOIN (select c.user_id,c.marketer_id,m.login_name,m.display_name,c.buyer_category

from TP_CRM_CUSTOMER c

INNER JOIN TP_USER m on c.marketer_id = m.user_id where c.del=0 and c.user_type = 1 and c.marketer_id = 40)

cc on o.b_user_id = cc.user_id

where o.order_dt between str_to_date('2015-07-01','%Y-%m-%d %H:%i:%s') and

   str_to_date('2015-08-01','%Y-%m-%d %H:%i:%s') and o.`status` not in (0,6,7,8) and 

b.createdTime between str_to_date('2015-07-01','%Y-%m-%d %H:%i:%s') and

   str_to_date('2015-08-01','%Y-%m-%d %H:%i:%s')

GROUP BY o.b_user_id  order by sum desc

7月订单商品表

select o.order_id,o.b_user_id,o.s_user_id,cc.display_name,cc.marketer_id,p.fcategory_id,o.mny,o.order_dt,p.prod_name,p.prod_num,p.prod_price,

p.prod_num*p.prod_price from TP_ORDER o INNER JOIN 

(select pd.order_id,dd.fcategory_id,dd.prod_name,pd.prod_num,pd.prod_price from TP_ORDER_PROD pd INNER JOIN TP_PROD dd on pd.prod_id = dd.id)

p on p.order_id = o.order_id 

INNER JOIN (select c.user_id,c.marketer_id,m.login_name,m.display_name,c.buyer_category 

from TP_CRM_CUSTOMER c

INNER JOIN TP_USER m on c.marketer_id = m.user_id where c.del=0 and c.user_type = 1)

cc on o.b_user_id = cc.user_id

where o.order_dt between str_to_date('2015-07-01','%Y-%m-%d %H:%i:%s') and

   str_to_date('2015-08-01','%Y-%m-%d %H:%i:%s') and o.`status` not in (0,6,7,8)