常用数据统计

select b.user_id,b.display_name,b.phone,a.consignee from 

(select distinct b_user_id , count(*) as count from TP_ORDER where order_dt > '2015-3-1' group by b_user_id) c 

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

INNER JOIN (select DISTINCT d.user_id ,d.consignee,d.address from TP_BUY_ADDR d group by d.user_id) a on a.user_id = c.b_user_id 

where c.count >9 ；

select count(*) from

(select distinct b_user_id , count(*) as count from TP_ORDER where order_dt > '2015-3-1' group by b_user_id) c

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

where c.count >9 

新增餐厅数

select * from TP_BUYER where createdTime between

str_to_date('2015-04-19','%Y-%m-%d %H:%i:%s') and

   str_to_date('2015-04-26','%Y-%m-%d %H:%i:%s') 

下单餐厅数

select count(DISTINCT b_user_id) from TP_ORDER where order_dt between

str_to_date('2015-04-19','%Y-%m-%d %H:%i:%s') and

   str_to_date('2015-04-26','%Y-%m-%d %H:%i:%s') 

每周实际产生交易的供应商数

select count(distinct s.user_id) FROM TP_ORDER o INNER JOIN TP_SALER s on o.s_user_id = s.user_id 

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

   str_to_date('2015-04-19','%Y-%m-%d %H:%i:%s')

当周老用户下单总金额统计

select sum(mny) from TP_ORDER o INNER JOIN TP_BUYER b on o.b_user_id = b.user_id 

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

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

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

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

select count(*) from TP_ORDER o INNER JOIN TP_BUYER b on o.b_user_id = b.user_id

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

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

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

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

下单留存率

select count(distinct b_user_id) FROM TP_ORDER 

where b_user_id in (select b_user_id from TP_ORDER WHERE order_dt 

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

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

and order_dt 

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

   str_to_date('2015-04-12','%Y-%m-%d %H:%i:%s')

前20名供应商列表统计

select * from (select distinct s_user_id ,SUM(mny) as mny from TP_ORDER where order_dt between 

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

   str_to_date('2015-04-12','%Y-%m-%d %H:%i:%s') and status not in (6,7,8)

GROUP BY s_user_id) c inner join TP_SALER b on b.user_id = c.s_user_id ORDER BY c.mny desc limit 23

订单金额区间统计

select count(*) from  TP_ORDER where order_dt between 

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

   str_to_date('2015-04-22','%Y-%m-%d %H:%i:%s') and status not in (6,7,8) and mny>2000

单品类采购金额

select sum(p.prod_num*p.prod_price) from TP_ORDER_PROD p 

INNER JOIN TP_ORDER o  ON o.order_id = p.order_id 

INNER JOIN TP_PROD d on d.id = p.prod_id 

where o.`status` not in (6,7,8)  and o.order_dt 

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

   str_to_date('2015-04-19','%Y-%m-%d %H:%i:%s')

 and d.fcategory_id = 2

导出某个卖家订单商品明细

SELECT p.order_id,o.recevier_name,o.address,o.telephone,d.prod_name,p.prod_price,p.prod_num from TP_ORDER_PROD p INNER JOIN TP_ORDER o on o.order_id = p.order_id 

inner join TP_PROD d on p.prod_id = d.id 

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

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

导出某个卖家的销售件数

SELECT sum(p.prod_num) from TP_ORDER_PROD p INNER JOIN TP_ORDER o on o.order_id = p.order_id 

left join TP_PROD d on p.prod_id = d.id 

where o.s_user_id = 36 and o.order_dt between str_to_date('2015-04-15','%Y-%m-%d %H:%i:%s') and

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

销售买家客户销售件数

SELECT t.b_user_id,t.prod_num,c.display_name,c.phone,c.sale_address 

FROM (SELECT o.b_user_id,SUM(p.prod_num) AS prod_num

FROM TP_ORDER_PROD p

INNER JOIN TP_ORDER o ON p.order_id = o.order_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')

GROUP BY o.b_user_id) t 

INNER JOIN TP_CRM_CUSTOMER c ON t.b_user_id = c.user_id AND c.user_type = 1 AND c.del = 0

 AND c.marketer_id = 46

SELECT p.order_id,o.display_name,o.sale_name,o.recevier_name,o.order_dt,o.address,o.telephone,d.prod_name,p.prod_price,p.prod_num from TP_ORDER_PROD p

INNER JOIN (select ord.order_id,ord.recevier_name,ord.address,s.display_name,s.sale_name,ord.s_user_id,ord.order_dt,

ord.`status`,ord.telephone from TP_ORDER ord INNER JOIN TP_SALER s on s.user_id = ord.s_user_id)

o on o.order_id = p.order_id

inner join TP_PROD d on p.prod_id = d.id  

where o.s_user_id =376 and 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) order BY o.s_user_id,o.order_dt

销量排名

select DISTINCT p.prod_id,d.prod_name,s.display_name,sum(p.prod_num) as sum ,sum(p.prod_num*p.prod_price) as price 

from TP_ORDER_PROD p INNER JOIN TP_PROD d on p.prod_id = d.id  

INNER JOIN TP_ORDER o on o.order_id = p.order_id 

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

where d.fcategory_id =1 and o.`status` not in (0,6,7,8) and s.`status` = 1 group by p.prod_id order by sum desc

第一次下单用户

select COUNT(*) from (select o.b_user_id,MIN(o.order_dt) as mins from TP_ORDER o GROUP BY o.b_user_id) s where s.mins 

 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')