ofbiz系统中Mysql

1、每周各类目销售额及其销售额占比；

 

 

select a.* ,b.week_total_Fee ,concat(a.total_Fee/b.week_total_Fee*100,'%')'每周各类目所占百分比' from
(select sum((oot.current_Total_Fee)) total_Fee,opc.category_Id,opc.Category_Name,DATE_FORMAT(end_date,'%x-%v') endDate
from OPEN_ORDER oo,OPEN_ORDER_ITEM oot,OPEN_PRODUCT op,OPEN_PRODUCT_CATEGORY opc
where oo.order_id =oot.order_id and oot.product_Id=op.product_Id and op.category_Id=opc.category_Id and op.is_Gift!=1 
and end_date is not null and end_date between '2010-08-01 00:00:00%' and '2010-09-30 23:59:59%'
and oot.status='completed' 
group by endDate,opc.category_Id 
order by endDate)a,
(
select sum((oot.current_Total_Fee)) week_total_Fee,DATE_FORMAT(end_date,'%x-%v') endDate
from OPEN_ORDER oo,OPEN_ORDER_ITEM oot,OPEN_PRODUCT op,OPEN_PRODUCT_CATEGORY opc
where oo.order_id =oot.order_id and oot.product_Id=op.product_Id and op.category_Id=opc.category_Id and op.is_Gift!=1 
and end_date is not null and end_date between '2010-08-01 00:00:00%' and '2010-09-30 23:59:59%'
and oot.status='completed' 
group by endDate
)b 
where a.endDate = b.endDate

 吧

2.鞋类已销售尺寸分布

select LEFT(ooi.product_id,INSTR(ooi.product_id,'-')-1) '产品货号',RIGHT(ooi.product_id,LENGTH(ooi.product_id)-INSTR(ooi.product_id,'-')) '尺码',count('尺码') '数量'
from open_order oo,open_order_item ooi,open_product op 
where oo.order_id = ooi.order_id and (oo.status != 'closed' and oo.status != 'replaced') 
and oo.end_date is not null and ooi.product_id = op.product_id and op.category_id = 'SHOE' group by '产品货号','尺码'

 3.各价格区间（50元为一区间）的交易额占交易总额的比例:

select concat(concat(ceil(total_fee/50)*50-50,'-'),ceil(total_fee/50)*50) '价格区间' ,
count(ceil(total_fee/50)*50) count  
from open_order where total_fee != 0
group by '价格区间'
order by total_fee 

 4.平均每日发货数

select avg(n) '平均每日发货数' from(
select count(order_id) n,DATE_FORMAT(end_date,'%y-%m-%d')
from open_order
where end_date is not null and status <> 'closed' and status <> 'canceled' and status <> 'replaced'
group by DATE_FORMAT(end_date,'%y-%m-%d')
)m

 5.