1. 经销商 按店铺交易量汇总
select i.shop_id,i.shop_name,u.real_name,u.mobile,nvl(p.summary,0) from bp_shop_info i left join
(
select t.shop_id,sum(t.shop_cost) as summary from bp_platform_order t
where t.request_time >= to_date('2014-**','yyyy-mm')
and t.request_time < to_date('2014-**','yyyy-mm')
and t.spreader_id=10210
and t.order_status in (0,91)
group by t.shop_id) p
on i.shop_id=p.shop_id
left join bp_system_user u on i.shop_owner=u.user_id
where i.spreader_id=10210
交易量汇总
select e.nickname staff,i.shop_name, u.real_name, u.mobile, nvl(p.summary, 0) total_order_fee
from bp_shop_info i
left join (select t.shop_id, sum(t.order_fee) as summary
from bp_platform_order t
where t.request_time >= to_date('2014-04', 'yyyy-mm')
and t.request_time < to_date('2014-05', 'yyyy-mm')
and t.spreader_id = 10210
and t.order_status in (0, 91)
and t.business_type in ('G3')
group by t.shop_id) p on i.shop_id = p.shop_id
left join bp_system_user u on i.shop_owner = u.user_id
left join bp_spreader_employee e on i.spreaders_staff_id=e.user_id
left join bp_system_spreader s on i.spreader_id=s.user_id
left join bp_system_user u2 on s.user_id=u2.user_id
where i.spreader_id = 10210
order by i.shop_id desc
按业务统计
张勇
select u2.real_name 推广人, i.shop_name 店铺名称, u.real_name 店主姓名, u.mobile 店主电话, nvl(p.summary, 0) 总交易量
from bp_shop_info i
left join (select t.shop_id, sum(t.shop_cost) as summary
from bp_platform_order t
where t.request_time >= to_date('2014-07', 'yyyy-mm')
and t.request_time < to_date('2014-08', 'yyyy-mm')
and t.spreader_id = 10210
and t.order_status in (0, 91)
and t.business_type in ('Huafei','Guhua')
group by t.shop_id) p on i.shop_id = p.shop_id
left join bp_system_user u on i.shop_owner = u.user_id
left join bp_system_user u2 on i.spreaders_staff_id = u2.user_id
where i.spreader_id = 10210
and i.create_time >= to_date('2014-07', 'yyyy-mm')
and i.create_time < to_date('2014-08', 'yyyy-mm')
order by i.shop_id desc
张勇_新开店铺话费交易量汇总
2. 供货商 查询供货商的订单列表
select t.platform_order_id,
t.finish_time,
t.product_name,
i.shop_name,
u.real_name,
t.system_cost,
t.platform_cost,
t.spreader_cost,
t.shop_cost,
t.shop_pay_fee
from bp_platform_order t
inner join bp_shop_info i on t.shop_id = i.shop_id
inner join bp_system_user u on t.spreader_id = u.user_id
where t.supplier_id = 15240
and t.spreader_id = 15240
and t.finish_time >= to_date('2014-**', 'yyyy-mm')
and t.finish_time < to_date('2014-**', 'yyyy-mm')
and t.order_status = 0
order by t.product_name desc
供货商订单列表
select u.real_name 供货商,
t.platform_order_id 订单号,
t.finish_time 订单完成时间,
t.product_name 商品名称,
i.shop_name 店铺名称,
t.system_cost 供货商家成本,
t.spreader_cost 经销商成本,
t.shop_cost 店铺成本,
t.shop_pay_fee 店铺支付金额,
t.order_num 总件数,
t.logistics_fee 运费
from bp_platform_order t
inner join bp_shop_info i on t.shop_id = i.shop_id
inner join bp_system_user u on t.spreader_id = u.user_id
where t.supplier_id = 10210
and t.spreader_id = 10210
and t.finish_time >= to_date('2014-06-18', 'yyyy-mm-dd')
and t.finish_time < to_date('2014-08-01', 'yyyy-mm-dd')
and t.success_fee > 0
and t.order_status = 0
order by t.finish_time asc
张勇_货款结算
3. 经销商 按店铺统计 实物商品的交易笔数
select e.nickname staff,i.shop_name, u.real_name, u.mobile, nvl(p.summary, 0) total_order_fee
from bp_shop_info i
left join (select t.shop_id, count(t.platform_order_id) as summary
from bp_platform_order t
inner join bp_goods_order_details d on t.platform_order_id=d.order_no
where t.request_time >= to_date('2014-04', 'yyyy-mm')
and t.request_time < to_date('2014-05', 'yyyy-mm')
and t.spreader_id = 10210
and t.order_status in (0, 91)
and d.sku_id=5
group by t.shop_id) p on i.shop_id = p.shop_id
left join bp_system_user u on i.shop_owner = u.user_id
left join bp_spreader_employee e on i.spreaders_staff_id=e.user_id
left join bp_system_spreader s on i.spreader_id=s.user_id
left join bp_system_user u2 on s.user_id=u2.user_id
where i.spreader_id = 10210
order by i.shop_id desc
交易笔数
4. 供货商结算（扣除平台钱包）
sp_pay_bianmin_withdraw
5. 店铺交易量汇总
* Pur:统计各产品线交易量，按店铺汇总
* Frm:来自张勇的需求
temp_rpt_sales_shop
6. 根据手机号码或姓名查用户相关角色及信息
select (case nvl(e.role_id, -1)
when 0 then
'经销商'
when 1 then
'推广人'
when -1 then
'/'
else
'经销商员工'
end) || '[' || nvl(e.se_id, -1) || ']' "经销商(SEID)",
nvl(si.name, '/') || '[' || nvl(si.suppiler_id, -1) || ']' "供货商[SUID]",
decode(se.role_id, 0, '店主', '店员') || '[' || i.shop_id || ']' "代理商[SHOPID]",
u.user_name || '[' || u.mobile || ']' "登陆名[手机]",
u.real_name || '[' || u.user_id || ']' "真名[UID]",
u2.real_name || '[' || u2.user_id || ']' 所属经销商,
u3.real_name || '[' || u3.user_id || ']' 所属推广人
from bp_system_user u
left join bp_shop_info i on u.shop_id = i.shop_id
left join bp_shop_employee se on u.user_id = se.user_id
left join bp_spreader_employee e on u.user_id = e.user_id
left join bp_supplier_info si on u.user_id = si.user_id
left join bp_system_user u2 on i.spreader_id = u2.user_id
left join bp_system_user u3 on i.spreaders_staff_id = u3.user_id
where u.real_name = ''
or u.mobile = ''
or u.user_name = ''
根据手机号码或姓名查用户相关角色及信息
7. 查询某天各个时段的充值量
select to_char((trunc(sysdate) +
trunc((o.request_time - trunc(sysdate)) * 24 * 60 / 30) * 30 / 60 / 24),
'hh24:mi') || '-' ||
to_char((trunc(sysdate) +
trunc((o.request_time - trunc(sysdate)) * 24 * 60 / 30 + 1) * 30 / 60 / 24),
'hh24:mi') period,
count(*) 订单笔数,
sum(o.order_fee) 订单总面值,
sum(o.success_fee) 成功总面值
from bp_platform_order o
where o.request_time >= to_date('', 'yyyymmdd')
and o.request_time < to_date('', 'yyyymmdd') + 1
group by to_char((trunc(sysdate) +
trunc((o.request_time - trunc(sysdate)) * 24 * 60 / 30) * 30 / 60 / 24),
'hh24:mi') || '-' ||
to_char((trunc(sysdate) +
trunc((o.request_time - trunc(sysdate)) * 24 * 60 / 30 + 1) * 30 / 60 / 24),
'hh24:mi')
order by 1;
8. 店铺通过资金变动和当天店铺交易报表中的成本来计算的当天资金消耗
公式：
x 当天计算的消费金额 = 第二天的余额 – 前一天的余额
y 当天店铺计算的订单成本 = 当天已完成订单的成本合计
y = x + (当天以前下的订单，当天完成的订单成本总计[SQL1]) – (当天下的订单，当天以后完成的订单成本总计[SQL2])
SQL1:
select * from bp_platform_order o
where o.request_time < to_date('','yyyymmdd')
and o.finish_time >= to_date('','yyyymmdd')
and o.finish_time < to_date('','yyyymmdd')
and o.shop_id=104652
SQL2:
select * from bp_platform_order o
where o.request_time >= to_date('','yyyymmdd')
and o.request_time < to_date('','yyyymmdd')
and o.finish_time >= to_date('','yyyymmdd')
and o.shop_id=104652
