sql

select a.yt 用途说明,hw.HW 仓位,b.productno 物料编号,b.productname 物料名称 ,a.num 转入数,a.shnum 入库,a.dj 送货单价,a.dj*a.shnum 金额, 
case when isnull(a.num,0)-isnull(a.shnum,0)> 0 then  isnull(a.num,0)-isnull(a.shnum,0) else null end 存,
a.outnum 出 ,a.shdate 收货时间,a.shdh 送货单号,a.indate 转入时间  , jldw.dwname 单位,a.weight 重量,fz.productno 辅助编号,fz.productname 辅助名称,
a.cw 财务 ,a.orderno 订单编号,a.ordersn 笔数,o.peitaosn 配套,a.supliername 供应商, a.pjhpz 票据凭证,cg.dj 采购价, b.productcgj 受控价,a.cgdh 采购单号,
a.shperson 收货人,a.shno 入库单号,a.ckname 仓库,cgcount.cgcount 采购次数,rkcount.rkcount 入库次数,dbcount.dbcount 出仓次数,db2count.db2count 出仓2次数,
a.person 转入人,a.outno 转出编号,a.outperson 转出人,a.outdate 转出时间,a.slid,a.id,a.source,a.sourcedw 来源单位,a.sourceid,a.cgtype,a.xq2id,a.oldslid, 
u.empname as 跟单人,ckname.ckname as 下单档口,b.newproductno , 
case when isnull(b.photo,'')='' then fz.photo else b.photo end as photo ,a.yck,
case when isnull(a.cgtype,0)=0 then '进仓1' 
when isnull(a.cgtype,0)=2 then '进仓2' when isnull(a.cgtype,0)=3 then '换进仓'  when isnull(a.cgtype,0)=4 then '退料进仓'  end as 进仓类别  
from rksq a left outer join product b on a.slid=b.id 
left outer join order o on a.orderno=o.orderno and a.ordersn=o.ordersn  
left outer join (select orderno,ordersn,slid,num,count(*) cgcount from cgwlsqdetail a group by orderno,ordersn,slid,num) cgcount   on a.orderno=cgcount.orderno and a.ordersn=cgcount.ordersn and a.slid=cgcount.slid and a.num=cgcount.num  
left outer join (select orderno,ordersn,slid, shnum num,count(*) rkcount from rksq a group by orderno,ordersn,slid,shnum) rkcount   on a.orderno=rkcount.orderno and a.ordersn=rkcount.ordersn and a.slid=rkcount.slid and a.num=rkcount.num  
left outer join (select distinct cgdh,dj,isnull(orderno,'') orderno,ordersn,slid from cgwlsqdetail) cg on a.cgdh=cg.cgdh and a.orderno=cg.orderno and a.ordersn=cg.ordersn and a.slid=cg.slid  
left outer join (select orderno,ordersn,slid,dbnum num,count(*) dbcount from dbwlsqdetail a group by orderno,ordersn,slid,dbnum) 
	dbcount   on a.orderno=dbcount.orderno and a.ordersn=dbcount.ordersn and a.slid=dbcount.slid and a.num=dbcount.num  
 left outer join (select orderno,ordersn,slid,dbnum num,count(*) db2count from dbwlsqdetail2 a group by orderno,ordersn,slid,dbnum)  db2count  on a.orderno=db2count.orderno and a.ordersn=db2count.ordersn and a.slid=db2count.slid and a.num=db2count.num  
 left outer join (select orderno,ordersn,slid, num,count(*) sfcount from sfsq a group by orderno,ordersn,slid,num) sfcount   on a.orderno=sfcount.orderno and a.ordersn=sfcount.ordersn and a.slid=sfcount.slid and a.num=sfcount.num  
 left outer join user u on o.gdr=u.id left outer join ckname ckname on o.ckorderid=ckname.id   
 left outer join product cp on o.productid=cp.id  
 left outer join jldw jldw on b.jldwid=jldw.id 
 left outer join ( select a.ID,a.ProductID,a.ckid,a.HW,a.Memo ,b.ckName  from HW a, ckName b where a.CKID =b.ID and b.ckname='商务' ) hw on a.SLID=hw.ProductID   
 left outer join product fz on b.fzcode = fz.productno  where 1=1 and a.ckname='商务'  and ( isnull(a.cgtype,0)=0 or ( isnull(a.cgtype,0)=2 ) )  and isnull(a.outnum,0)=0  order by a.id desc