insert into cb (orderno,ordersn,cbtype,slcb,pjcb,rgfscb,rgktcb,rgxscb,rgzscb,rgbzcb,ddcb,slsycb,ktgz,xsgz,zsgz,bzgz,wfj,cbycyy)
select orderno,ordersn,cbtype,slcb,pjcb,fscb,ktcb,xscb,zscb,bzcb,ddcb,slsycb,ktgz,xsgz,zsgz,bzgz,wfprice,cbycyy from (select a.orderno,a.ordersn,a.cbtype,isnull(fs.cbycyy,'')+isnull(sl.cbycyy,'')+isnull(pj.cbycyy,'')+isnull(rg.cbycyy,'')+isnull(dd.cbycyy,'')+case when a.iswf=1 and isnull(wfj.price,0)=0 then '外发价格为零' else '' end cbycyy,
wfj.price wfprice,fs.fscb,sl.slcb,pj.pjcb,rg.ktcb,rg.xscb,rg.zscb,rg.bzcb,dd.ddcb,sycb.sycb/a.num slsycb,rggz.ktgz , rggz.xsgz, rggz.zsgz, rggz.bzgz
From
(select a.orderno,a.ordersn,a.num,a.productid,0 cbtype,a.jgs,a.iswf,a.orderdate
from order a where num>0 and isnull(isch,0)=0
union all
select a.orderno,a.ordersn,a.num,a.productid,1 cbtype,a.jgs,a.iswf,a.orderdate
from order a where num>0 and isnull(isch,0)=0 ) a
left outer join
(select a.orderno,a.ordersn,case when b.jgs<>'工厂' then 1 else 0 end cbtype,max(case when
b.id is null then '空台材料与 BOM 不符' else '' end) cbycyy,
sum(case when isnull(b.price,0)=0 then c.pjnum*c.bomweight*d.cbj2 else b.price*c.pjnum*c.bomweight end) fscb
from order a
inner join bom c on a.productid=c.productid
inner join product d on
d.id=c.pjid
left outer join orderktrg b on a.orderno=b.orderno and a.ordersn=b.ordersn and c.pjid=b.rgid
where d.productno like 'K%' and a.orderno = 'S0116050030' and a.ordersn=10 group by a.orderno,a.ordersn,b.jgs) fs
on a.orderno=fs.orderno and a.ordersn=fs.ordersn and a.cbtype=fs.cbtype
left outer join
(select a.orderno,a.ordersn,max(case when c.orderno is not null and isnull(c.wlly,'')<>'客户' and isnull(c.cbj2,0)=0 and isnull(b.cbj2,0)=0 and isnull(b.rkprice,0)=0 then '价格为零' else '' end)+case when isnull(need.need,0) <> cc.slnumtotal*a.num then '数量不符' else '' end cbycyy,
case when c.wlly<>'工厂' then 1 else 0 end cbtype,sum(c.num*case when isnull(c.cbj2,0)>0 then c.cbj2 when isnull(b.cbj2,0)<isnull(b.rkprice,0) then b.rkprice else b.cbj2 end)/case when a.num=0 then null else a.num end slcb
from order a left outer join orderslxq c on a.orderno=c.orderno and a.ordersn=c.ordersn and c.wlly<>'客户'
left outer join product b on c.slid=
b.id inner join product cc on a.productid=
cc.id left outer join
(select orderno,ordersn,sum(num) need
from orderslxq a
where 1=1 and a.orderno = 'S0116050030' and a.ordersn=10 group by a.orderno,a.ordersn
) need on a.orderno=need.orderno and a.ordersn=need.ordersn
where 1=1 and a.orderno = 'S0116050030' and a.ordersn=10
group by a.orderno,a.ordersn,a.num,c.wlly,cc.slnumtotal,need.need) sl
on a.orderno=sl.orderno and a.ordersn=sl.ordersn and a.cbtype=sl.cbtype
left outer join
(select a.orderno,a.ordersn,max(case when isnull(b.cbj2,0)=0 and isnull(b.rkprice,0)=0 then '配件价格为零' else '' end)+ case when need.need <> cc.pjnumtotal*c.num then '数量不符' else '' end cbycyy,
case when a.wlly<>'工厂' then 1 else 0 end cbtype,sum(a.num*case when isnull(b.cbj2,0)<isnull(b.rkprice,0) then b.rkprice else b.cbj2 end)/case when c.num=0 then null else c.num end pjcb
from orderpjxq a
inner join product b on a.xxid=
b.id inner join order c on a.orderno=c.orderno and a.ordersn=c.ordersn
inner join product cc on c.productid=
cc.id inner join (select orderno,ordersn,sum(num) need
from orderpjxq a where 1=1 and a.orderno = 'S0116050030' and a.ordersn=10 group by a.orderno,a.ordersn) need on a.orderno=need.orderno and a.ordersn=need.ordersn
where 1=1 and a.wlly<>'客户' and a.orderno = 'S0116050030' and a.ordersn=10 group by a.orderno,a.ordersn,c.num,a.wlly,cc.pjnumtotal,need.need) pj
on a.orderno=pj.orderno and a.ordersn=pj.ordersn and a.cbtype=pj.cbtype
left outer join
(select a.orderno,a.ordersn,max(case when
b.id is null and d.productname not like '%镶%' then '项目不符' when d.productname like '%镶%' and a.orderdate>='2012-03-21' and isnull(xsrg2.ycyy,0)>0 then '人工项目与 BOM 不符' else '' end) cbycyy,
case when b.jgs<>'工厂' then 1 else 0 end cbtype,
sum(case when d.productno like 'R%' and d.productname not like '%镶%' and d.productname not like '%包装%' and d.productname not like '%抹黑%' and d.productname not like '%粘%' and d.productname not like '%滴%' then case when isnull(b.price,0)=0 then c.bomprice
else b.price end*c.pjnum*
c.bs else Null end ) ktcb,
case when a.orderdate>='2012-03-21' then xsrg2.xsrg when isnull(xsrg2.xsrg,0)=0 then sum(case when d.productno like 'R%' and d.productname like '%镶%' then case when isnull(b.price,0)=0 then c.bomprice else b.price end*c.pjnum*
c.bs else Null end ) else xsrg2.xsrg end xscb,
sum(case when d.productno like 'R%' and (d.productname like '%粘%' or d.productname like '%抹黑%' or d.productname like '%滴%') then case when isnull(b.price,0)=0 then c.bomprice else b.price end*c.pjnum*
c.bs else Null end ) zscb,
sum(case when d.productno like 'R%' and d.productname like '%包装%' then case when isnull(b.price,0)=0 then c.bomprice else b.price end*c.pjnum*
c.bs else Null end ) bzcb
from order a inner join bom c on a.productid=c.productid
inner join product d on
d.id=c.pjid
left outer join orderktrg b on a.orderno=b.orderno and a.ordersn=b.ordersn and c.pjid=b.rgid
left outer join (select a.orderno,a.ordersn,
case when isnull(a.jgs,'')<>'工厂' and isnull(a.jgs,'')<>'' then 1 else 0 end cbtype,
sum(case when isnull(a.price_jgs,0)=0 and isnull(b.price_bom,0)=0 then 1 else 0 end) ycyy,
sum(case when isnull(a.price_jgs,0)=0 then b.price_bom else a.price_jgs end* a.num/case when isnull(c.num,0)=0 then null else c.num end) xsrg
from orderslxq a
inner join product p on a.slid=
p.id and p.productno not like 'P%'
left outer join gyname b on a.gyid=
b.id inner join order c on a.orderno=c.orderno and a.ordersn=c.ordersn where 1=1 and a.orderno like '%S0116050030%' and a.ordersn=10 and isnull(a.gxid,0)<>6
group by a.orderno,a.ordersn,a.jgs) xsrg2 on a.orderno=xsrg2.orderno and a.ordersn=xsrg2.ordersn and case when b.jgs<>'工厂' then 1 else 0 end=xsrg2.cbtype where 1=1 and d.productno like
'R%' and a.orderno like '%S0116050030%' and a.ordersn=10 group by a.orderdate,a.orderno,a.ordersn,xsrg2.ycyy,xsrg2.xsrg,
case when b.jgs<>'工厂' then 1 else 0 end) rg
on a.orderno=rg.orderno and a.ordersn=rg.ordersn and a.cbtype=rg.cbtype
left outer join
(select a.orderno,a.ordersn,a.cbtype,a.productid,max(CASE WHEN a.ddprice=0 then '价格为零' else '' end) cbycyy,
case when SUM(a.ddnum)=0 then sum(1*a.ddprice) else SUM(a.ddnum * a.ddprice) / case when SUM(a.ddnum)=0 then 1 else sum(a.ddnum) end end as ddcb,
case when SUM(a.ddnum)=0 then 1 else sum(a.ddnum) end as ddnum
From (SELECT a.orderno, a.ordersn,a.cbtype,a.productid,a.ddnum,a.cureid,
case when isnull(a.curprice,0)>0 then a.curprice when
c.id is not null then c.ddprice when
c1.id is not null then c1.ddprice when
c2.id is not null then c2.ddprice else 0 end as ddprice
FROM (SELECT a.orderno, a.ordersn,case when isnull(b.jgs, '')<>'工厂' and
isnull(b.jgs,'')<>'' then 1 else 0 end cbtype,b.cureid, b.platcorlor, b.ddnum, b.jgs,C.ProductID , C.Productname, C.productid1, C.productid2, b.curprice
FROM order a
inner JOIN orderdd b ON a.orderno = b.orderno AND a.ordersn = b.ordersn and b.platcorlor<>'铜'
INNER JOIN
(select
a.id as productid,a.photo,a.productname,a.productno,
b.id as productid1,
c.id as productid2
from product a
left outer join product b on replace(CASE WHEN len(a.productno)- len(replace(a.productno, '-', '')) > 1 THEN LEFT(a.productno,charindex('-', a.productno, charindex('-', a.productno, 1) + 1) - 1) ELSE a.productno end,'A','')+'A'=b.productno
left outer join product c on replace(CASE WHEN len(a.productno)- len(replace(a.productno, '-', '')) > 1 THEN LEFT(a.productno,charindex('-', a.productno, charindex('-', a.productno, 1) + 1) - 1) ELSE a.productno end,'A','')=c.productno ) c
ON a.productid = c.productid) a
left outer join productddprice C ON (a.productid=c.productid) AND a.cureid = c.cureid AND a.platcorlor = c.platcorlor
left outer join productddprice c1 ON (a.productid1=c1.productid) AND a.cureid = c1.cureid AND a.platcorlor = c1.platcorlor
left outer join productddprice c2 ON (a.productid2=c2.productid) AND a.cureid = c2.cureid AND A.PlatCorlor = c2.PlatCorlor) a
where 1=1 and a.orderno like '%S0116050030%' and a.ordersn=10 group by a.orderno,a.ordersn,a.productid,a.cbtype) dd
on a.orderno=dd.orderno and a.ordersn=dd.ordersn and a.cbtype=dd.cbtype
left outer join
(select sycb.orderno,sycb.ordersn,case when isnull(sycb.sycb,0)>isnull(tlcb.tlcb,0) then isnull(sycb.sycb,0)-isnull(tlcb.tlcb,0) else null end sycb from (select orderno,ordersn,sum(cb) sycb
From
(select orderno,ordersn,outnum*case when isnull(b.cbj2 ,0)<isnull(b.rkprice,0) then b.rkprice else b.cbj2 end cb
from dbwlsqdetail a
left outer join product b on a.slid=
b.id and isnull(a.outnum,0)>0 where 1=1 and a.orderno like '%S0116050030%' and a.ordersn=10
Union All
select orderno,ordersn,outnum*case when isnull(b.cbj2 ,0)<isnull(b.rkprice,0) then b.rkprice else b.cbj2 end cb
from dbwlsqdetail2 a
left outer join product b on a.slid=
b.id and isnull(a.outnum,0)>0
where a.orderno ='??????' and a.ordersn=10 ) a group by orderno,ordersn) sycb
left outer join
(select a.orderno,a.ordersn,sum(a.shnum*case when isnull(b.cbj2 ,0)<isnull(b.rkprice,0) then b.rkprice else b.cbj2 end) tlcb
from rksq a
inner join product b on a.slid=
b.id and isnull(a.shnum,0)>0 where isnull(cgdh,'')='' and a.orderno like '%S0116050030%' and a.ordersn=10
group by a.orderno,a.ordersn) tlcb
on sycb.orderno=tlcb.orderno and sycb.ordersn=tlcb.ordersn) sycb
on a.orderno=sycb.orderno and a.ordersn=sycb.ordersn and a.cbtype=0
left outer join
(select a.orderno,a.ordersn,'' cbycyy,sum(case when d.productno like 'R%' and d.productname not like '%镶%' and d.productname not like '%包装%' and
d.productname not like '%抹黑%' and d.productname not like '%粘%' and d.productname not like '%滴%' then b.productnum*b.storenum*b.gxprice else Null end) ktgz,
sum(case when e.gyname like '%镶%' then b.productnum*b.storenum*b.gxprice else Null end) xsgz,
sum(case when d.productno like 'R%' and (d.productname like '%粘%' or d.productname like '%抹黑%' or d.productname like '%滴%') then b.productnum*b.storenum*b.gxprice else Null end) zsgz,
sum(case when d.productno like 'R%' and d.productname like '%包装%' then b.productnum*b.storenum*b.gxprice else Null end) bzgz
from order a
inner join productnote b on a.orderno=b.orderno and a.ordersn=b.ordersn
left outer join bom c on a.productid=c.productid and b.rgid=c.pjid
left outer join product d on b.rgid=
d.id left outer join gyname e on b.gyid=
e.id where 1=1 and a.orderno like '%S0116050030%' and a.ordersn=10 group by a.orderno,a.ordersn) rggz
on a.orderno=rggz.orderno and a.ordersn=rggz.ordersn and a.cbtype=0
left outer join cure jgs ON a.jgs = jgs.curename
LEFT OUTER JOIN wfprice wfj ON a.productid = wfj.productid AND
jgs.id = wfj.cureid
where 1=1 and a.orderno like '%S0116050030%' and a.ordersn=10 ) cb
嗯，写得完美。
但是结果 select 那个结果为空了。尼玛。
开始找问题。用了一个小时才在换行，把层级结构搞清楚一点。然后一段一段试。你一个 insert 语句写这么复杂是要闹哪样。分段来不行么！！！！！！