查看所有未销完的手机订货单.sql

手机进销存系统Delphi源码,管理手机的进货还有销售方面的功能

/************************************
查看所有未销完的手机订货单
*************************************/
select b.* from (

select a.*,isnull(b.fcount,0) as fcount  from VAllsjrc a left outer join (

--每种进货的订单中已销的手机统计数,含两部分，（销售，退库）
select a.fid, sum(a.fcount) as fcount
from 
(
--得到每一个入库单ID的所有已销的机子数
select thrc.fid,count(*)as fcount from thxc,thrxx,thrc
		where thxc.fcode=thrxx.fid and thrc.fid=thrxx.fhrcid  
		group by thrc.fid

  union 
----得到每一个入库单ID的所有退库的机子数
select thrc.fid,count(*) as fcount
FROM tbs,thrxx,thrc
 WHERE  thrxx.fhrcid=thrc.fid and tbs.fhrxx =thrxx.fid and (dbo.tbs.fhrxx NOT IN
          (SELECT fhrxx
         FROM thhdd)) AND (dbo.tbs.fhrxx NOT IN
          (SELECT fhrxx
         FROM tkhth))and tbs.fid not in(select fbs from tbsfh)
group by thrc.fid
) a
group by a.fid 

) b  on a.fid= b.fid

)b
where b.fnum>b.fcount and b.fnum>1