select t_storehouse.storehousename,
t_product.typename,
temp.sum
from t_storehouse,
t_product,
(select temp1.storehouseid,
temp1.commodityid,
sum(sum) sum
from (select storehouseid,
commodityid,
sum(count) sum
from t_commodityoperate
where t_commodityoperate.operatetype = 'in'
group by storehouseid,commodityid
union
select storehouseid,
commodityid,
-sum(count) sum
from t_commodityoperate
where t_commodityoperate.operatetype = 'out'
group by storehouseid,commodityid) temp1
group by temp1.storehouseid,temp1.commodityid) temp
where temp.storehouseid = t_storehouse.id and
temp.commodityid = t_product.typeid