销售下单表

select DISTINCT equip_id,COUNT(*) from TP_ORDER GROUP BY equip_id 

机器码查询

select o.order_id,o.equip_id,o.mny,o.order_dt,s.display_name,s.user_id,cc.marketer_id,cc.display_name,

b.display_name,b.user_id,b.phone  from TP_ORDER o  

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

INNER JOIN TP_SALER s on o.s_user_id = s.user_id 

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

INNER JOIN TP_USER m on c.marketer_id = m.user_id where c.del=0 ) cc on o.b_user_id = cc.user_id 

where o.equip_id is not null and CHAR_LENGTH(o.equip_id) >0

select o.order_id,o.equip_id,o.mny,o.order_dt,s.display_name,s.user_id,cc.marketer_id,cc.display_name,

b.display_name,b.user_id,b.phone  from TP_ORDER o  

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

INNER JOIN TP_SALER s on o.s_user_id = s.user_id

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

INNER JOIN TP_USER m on c.marketer_id = m.user_id where c.del=0 ) cc on o.b_user_id = cc.user_id

where o.equip_id is not null and CHAR_LENGTH(o.equip_id) >0 and o.order_dt 

between str_to_date(' 2015-05-25','%Y-%m-%d %H:%i:%s') and

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

//统计某个销售的订单数据

select o.order_id,s.display_name,o.recevier_name,o.telephone,b.display_name,o.b_user_id,o.order_dt,cc.display_name from TP_ORDER o INNER JOIN TP_SALER s on o.s_user_id = s.user_id 

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

INNER JOIN (select c.user_id,u.display_name from TP_CRM_CUSTOMER c INNER JOIN TP_USER u on c.marketer_id = u.user_id 

where c.user_type=1 and c.del = 0 and c.marketer_id = 33) cc  on cc.user_id = o.b_user_id

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

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

//统计某销售的订单流水

select sum(o.mny) from TP_ORDER o 

INNER JOIN (select c.user_id,u.display_name from TP_CRM_CUSTOMER c INNER JOIN TP_USER u on c.marketer_id = u.user_id

where c.user_type=0 and c.del = 0 and c.marketer_id = 38) cc  on cc.user_id = o.s_user_id

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

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

select distinct o.b_user_id, from TP_ORDER o INNER JOIN 

(select d.order_id as order_id from TP_ORDER_PROD d INNER JOIN TP_PROD pd on d.prod_id = pd.id where pd.fcategory_id in (1988,1989) )

 p on o.order_id = p.order_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 GROUP BY o.b_user_id 

//订单数据

select o.order_id,o.order_dt,s.user_id,s.display_name,s. cc.display_name,o.b_user_id,b.display_name, o.equip_id,

CASE WHEN b.buyer_category =5 THEN '餐厅' WHEN b.buyer_category =7 THEN '其他'  ELSE '' END 

from TP_ORDER o INNER JOIN TP_SALER s on o.s_user_id = s.user_id 

INNER JOIN TP_BUYER b on b.user_id = o.b_user_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-05-31','%Y-%m-%d %H:%i:%s') and

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

select o.order_id,o.order_dt,s.user_id,s.display_name,cc.display_name,o.b_user_id,b.display_name,

CASE WHEN b.buyer_category =5 THEN '餐厅' WHEN b.buyer_category =7 THEN '其他'  ELSE '' END 

from TP_ORDER o INNER JOIN TP_SALER s on o.s_user_id = s.user_id 

INNER JOIN TP_BUYER b on b.user_id = o.b_user_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-05-31','%Y-%m-%d %H:%i:%s') and

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

and order_id in (select t.order_id from (select od.order_id,od.order_dt,od.`status` from TP_ORDER od ORDER BY order_dt LIMIT 1) t where 

t.order_dt between str_to_date(' 2015-05-31','%Y-%m-%d %H:%i:%s') and

   str_to_date(' 2015-06-15','%Y-%m-%d %H:%i:%s') and t.`status` not in (6,7,8)

)

//新用户订单

select o.order_id,o.order_dt,s.user_id,s.display_name,cc.display_name,o.b_user_id,b.display_name,o.equip_id,

CASE WHEN b.buyer_category =5 THEN '餐厅' WHEN b.buyer_category =7 THEN '其他'  ELSE '' END

from TP_ORDER o INNER JOIN TP_SALER s on o.s_user_id = s.user_id

INNER JOIN TP_BUYER b on b.user_id = o.b_user_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-06-01','%Y-%m-%d %H:%i:%s') and

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

and order_id in (select t.order_id from (select distinct od.b_user_id,od.order_id,od.order_dt,od.`status` from TP_ORDER od GROUP BY od.b_user_id

ORDER BY od.order_dt) t where

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

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

//餐厅用户查询

select distinct b.user_id,b.display_name,d.consignee,b.phone,cc.display_name,

CASE WHEN b.buyer_category =5 THEN '餐厅' WHEN b.buyer_category =7 THEN '其他'  ELSE '' END

 from TP_BUYER b

LEFT JOIN TP_BUY_ADDR d on b.user_id =d.user_id

LEFT 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 b.user_id = cc.user_id 

where b.createdTime between str_to_date(' 2014-05-31','%Y-%m-%d %H:%i:%s') and

   str_to_date(' 2015-07-01','%Y-%m-%d %H:%i:%s') GROUP BY b.user_id 

 order by b.user_id

//卖家订单数、金额 最近下单查询

select s.user_id,s.display_name,s.address,s.phone,s.createdTime,cc.display_name,sum(o.mny),count(o.order_id),max(o.order_dt) from TP_SALER s 

INNER JOIN TP_ORDER o on s.user_id = o.s_user_id 

LEFT 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 = 0) cc on o.b_user_id = cc.user_id 

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

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

group by s.user_id ORDER BY s.createdTime

销售客户的下单按买家汇总金额表

select b.user_id,b.display_name,cc.display_name,sum(o.mny) as mny from TP_BUYER b inner join TP_ORDER o on b.user_id = o.b_user_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 = 73) 

cc on b.user_id = cc.user_id 

where o.order_dt between str_to_date(' 2014-06-15添加到日历','%Y-%m-%d %H:%i:%s') and

   str_to_date(' 2015-07-15','%Y-%m-%d %H:%i:%s') 

GROUP BY o.b_user_id order by mny desc