xdmy/doc/sql/t.sql

select t.*,( reportery-(sumLaid+sumquit-sumuse-sumrefund) ) diff from 
(
select storage.*, 
ifnull(laid.sumLaid,0) sumLaid,
ifnull(u.sumuse,0) sumuse,
ifnull(quit.sumquit,0) sumquit,
ifnull(refund.sumrefund,0) sumrefund
from 
(
SELECT pr.contractid,pr.partcode,  pr.reportery
FROM part_repertory pr 
join warehouse w on w.id = pr.locationId
WHERE  w.`pastureId` = 31 
)storage
 left join
 (
SELECT pr.contractid,sum(pl.storageAmount  )sumLaid
FROM part_repertory pr 
join warehouse w on w.id = pr.locationId
left join partlaid pl on pr.contractId   =pl.contractId  and  pr.locationId = pl.locationId
WHERE   w.`pastureId` = 31  
group by pr.contractId 
)laid  on storage.contractId = laid.contractId
left join
(
SELECT pr.contractid,sum(pu.checkoutNumber) sumuse
FROM part_repertory pr 
join warehouse w on w.id = pr.locationId
left join partuse pu on pr.contractId   =pu.contractId  and  pr.locationId = pu.locationId
WHERE   w.`pastureId` = 31  
group by pr.contractId
)u on storage.contractId = u.contractid
left join
(
SELECT pr.contractid ,sum(pq.quitNumber)sumquit
FROM part_repertory pr 
join warehouse w on w.id = pr.locationId
left join partquit pq on pr.contractId   =pq.contractId   
join bigpartquit bpq on bpq.id =pq.bigid and bpq.pastureid =w.pastureId
WHERE  w.`pastureId` = 31  
group by pr.contractId
)quit on storage.contractid =quit.contractid
left join
(
SELECT pr.contractid,sum(pf.refundNumber)sumrefund
FROM  part_repertory pr 
join warehouse w on w.id = pr.locationId
left join partrefund pf on pr.contractId   =pf.contractId  
join   bigpartrefund   bpf on pf.bigid =bpf.id  and bpf.pastureid =w.pastureId
WHERE  w.`pastureId` = 31  
group by pr.contractId
)refund on refund.contractId =storage.contractId
)t