常用SQL语句(工作)
1. 经销商 按店铺交易量汇总
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 select e.nickname staff,i.shop_name, u.real_name, u.mobile, nvl(p.summary, 0) total_order_fee 2 from bp_shop_info i 3 left join (select t.shop_id, sum(t.order_fee) as summary 4 from bp_platform_order t 5 where t.request_time >= to_date('2014-04', 'yyyy-mm') 6 and t.request_time < to_date('2014-05', 'yyyy-mm') 7 and t.spreader_id = 10210 8 and t.order_status in (0, 91) 9 and t.business_type in ('G3') 10 group by t.shop_id) p on i.shop_id = p.shop_id 11 left join bp_system_user u on i.shop_owner = u.user_id 12 left join bp_spreader_employee e on i.spreaders_staff_id=e.user_id 13 left join bp_system_spreader s on i.spreader_id=s.user_id 14 left join bp_system_user u2 on s.user_id=u2.user_id 15 where i.spreader_id = 10210 16 order by i.shop_id desc
张勇
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 select u2.real_name 推广人, i.shop_name 店铺名称, u.real_name 店主姓名, u.mobile 店主电话, nvl(p.summary, 0) 总交易量 2 from bp_shop_info i 3 left join (select t.shop_id, sum(t.shop_cost) as summary 4 from bp_platform_order t 5 where t.request_time >= to_date('2014-07', 'yyyy-mm') 6 and t.request_time < to_date('2014-08', 'yyyy-mm') 7 and t.spreader_id = 10210 8 and t.order_status in (0, 91) 9 and t.business_type in ('Huafei','Guhua') 10 group by t.shop_id) p on i.shop_id = p.shop_id 11 left join bp_system_user u on i.shop_owner = u.user_id 12 left join bp_system_user u2 on i.spreaders_staff_id = u2.user_id 13 where i.spreader_id = 10210 14 and i.create_time >= to_date('2014-07', 'yyyy-mm') 15 and i.create_time < to_date('2014-08', 'yyyy-mm') 16 order by i.shop_id desc
2. 供货商 查询供货商的订单列表
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 select t.platform_order_id, 2 t.finish_time, 3 t.product_name, 4 i.shop_name, 5 u.real_name, 6 t.system_cost, 7 t.platform_cost, 8 t.spreader_cost, 9 t.shop_cost, 10 t.shop_pay_fee 11 from bp_platform_order t 12 inner join bp_shop_info i on t.shop_id = i.shop_id 13 inner join bp_system_user u on t.spreader_id = u.user_id 14 where t.supplier_id = 15240 15 and t.spreader_id = 15240 16 and t.finish_time >= to_date('2014-**', 'yyyy-mm') 17 and t.finish_time < to_date('2014-**', 'yyyy-mm') 18 and t.order_status = 0 19 order by t.product_name desc
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 select u.real_name 供货商, 2 t.platform_order_id 订单号, 3 t.finish_time 订单完成时间, 4 t.product_name 商品名称, 5 i.shop_name 店铺名称, 6 t.system_cost 供货商家成本, 7 t.spreader_cost 经销商成本, 8 t.shop_cost 店铺成本, 9 t.shop_pay_fee 店铺支付金额, 10 t.order_num 总件数, 11 t.logistics_fee 运费 12 from bp_platform_order t 13 inner join bp_shop_info i on t.shop_id = i.shop_id 14 inner join bp_system_user u on t.spreader_id = u.user_id 15 where t.supplier_id = 10210 16 and t.spreader_id = 10210 17 and t.finish_time >= to_date('2014-06-18', 'yyyy-mm-dd') 18 and t.finish_time < to_date('2014-08-01', 'yyyy-mm-dd') 19 and t.success_fee > 0 20 and t.order_status = 0 21 order by t.finish_time asc
3. 经销商 按店铺统计 实物商品的交易笔数
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 select e.nickname staff,i.shop_name, u.real_name, u.mobile, nvl(p.summary, 0) total_order_fee 2 from bp_shop_info i 3 left join (select t.shop_id, count(t.platform_order_id) as summary 4 from bp_platform_order t 5 inner join bp_goods_order_details d on t.platform_order_id=d.order_no 6 where t.request_time >= to_date('2014-04', 'yyyy-mm') 7 and t.request_time < to_date('2014-05', 'yyyy-mm') 8 and t.spreader_id = 10210 9 and t.order_status in (0, 91) 10 and d.sku_id=5 11 group by t.shop_id) p on i.shop_id = p.shop_id 12 left join bp_system_user u on i.shop_owner = u.user_id 13 left join bp_spreader_employee e on i.spreaders_staff_id=e.user_id 14 left join bp_system_spreader s on i.spreader_id=s.user_id 15 left join bp_system_user u2 on s.user_id=u2.user_id 16 where i.spreader_id = 10210 17 order by i.shop_id desc
4. 供货商结算(扣除平台钱包)
sp_pay_bianmin_withdraw
5. 店铺交易量汇总
* Pur:统计各产品线交易量,按店铺汇总
* Frm:来自张勇的需求
temp_rpt_sales_shop
6. 根据手机号码或姓名查用户相关角色及信息
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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 = '13071226209' or u.mobile = '13071226209' or u.user_name = '13071226209'
7. 查询某天各个时段的充值量
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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('20150119', 'yyyymmdd') and o.request_time < to_date('20150119', '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:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
select * from bp_platform_order o where o.request_time < to_date('20150202','yyyymmdd') and o.finish_time >= to_date('20150202','yyyymmdd') and o.finish_time < to_date('20150203','yyyymmdd') and o.shop_id=104652
SQL2:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
select * from bp_platform_order o where o.request_time >= to_date('20150202','yyyymmdd') and o.request_time < to_date('20150203','yyyymmdd') and o.finish_time >= to_date('20150203','yyyymmdd') and o.shop_id=104652