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