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元は1区間)の取引額が取引総額に占める割合:

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.平均1日あたりの出荷数

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.