xdmy/doc/sql/t2.sql

select * from (
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 p.name, w.pastureid, pr.contractid,pr.partcode, pv.providername  ,pr.brand, pr.reportery
FROM part_repertory pr 
join warehouse w on w.id = pr.locationId  
left join pasture p on pr.pastureid =p.id
left join provider pv on pr.providerid  =pv.id
where w.pastureId=31
)storage
left join
(
SELECT w.pastureid, 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  
group by pr.contractId,w.pastureid 
)laid  on storage.contractId = laid.contractId and storage.pastureid =laid.pastureid
left join
(
SELECT w.pastureid, 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  
group by pr.contractId,w.pastureid
)u on storage.contractId = u.contractid and storage.pastureid =u.pastureid
left join
(
SELECT w.pastureid, 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  
group by pr.contractId,w.pastureid
)quit on storage.contractid =quit.contractid and storage.pastureid =quit.pastureid
left join
(
SELECT w.pastureid, 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  
group by pr.contractId,w.pastureid
)refund on refund.contractId =storage.contractId and storage.pastureid =refund.pastureid
)t
)t2 where diff <>0


select