这是一个创建于 2966 天前的主题，其中的信息可能已经有所发展或是发生改变。
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 语句写这么复杂是要闹哪样。分段来不行么！！！！！！
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 语句写这么复杂是要闹哪样。分段来不行么！！！！！！
 |
1
jydeng 2016-10-05 11:55:04 +08:00
哈哈哈，这 SQL 写的 6
|
 |
2
lemontang 2016-10-05 11:56:21 +08:00 via Android
其实楼主要的是我们的吐槽吧
|
 |
3
geeglo 2016-10-05 11:56:23 +08:00
=.= 看晕了，里面似乎还带 逻辑部分？
|
 |
5
subpo 2016-10-05 12:00:05 +08:00
就问你怎么写逻辑写到比原生 sql 快？
|
 |
6
ebony0319 OP @subpo 传过来就是一堆字符串了。出了一点问题要查出来太难了。我觉得一段一段 update 比这个好。至少能够快速定位哪里出错了。
|
 |
7
Troevil 2016-10-05 12:11:17 +08:00
66666 这个 sql debug 起来不是一般的困难
|
 |
8
viko16 2016-10-05 12:29:28 +08:00
啊，好像在读英文文章
|
 |
9
22too 2016-10-05 12:40:46 +08:00
突然感觉，这个 sql 写了几天吧，然后调试花费了半个月。
|
 |
10
rannnn 2016-10-05 12:46:50 +08:00
真的不是自动生成出来的吗
|
 |
11
tabris17 2016-10-05 12:47:27 +08:00  1
为什么前端要写 SQL ？
|
 |
12
x86 2016-10-05 12:47:58 +08:00 via iPhone
写成这样也是 666
|
 |
13
amon 2016-10-05 12:56:14 +08:00 via iPhone
前端好辛苦，居然还写 SQL ，莫非是传说中的拍黄片？
|
 |
14
aploium 2016-10-05 13:08:12 +08:00
lz 这是半年份的膝盖请收好 (支持殴打写它的那个人)
|
 |
15
loading 2016-10-05 13:18:16 +08:00 via Android
手机滚了半天……
|
 |
16
scnace 2016-10-05 13:32:33 +08:00 via Android
估计 leetcode 上刷 sql 的题刷惯了 hhhh
|
 |
17
Infernalzero 2016-10-05 13:35:38 +08:00
这种 sql 语句一般不是人写出来的，是用框架生成的，前端不懂数据库这块肯定是直接用框架抽象成表达式调用的
|
|
18
ebony0319 OP @Infernalzero 我倒前端看了。确实是手写的。
|
 |
19
ByZHkc3 2016-10-05 14:26:13 +08:00
前端还要写 SQL ？
|
 |
20
zsx 2016-10-05 16:52:49 +08:00
这条 SQL ……一次要查多久……
|
 |
21
letitbesqzr 2016-10-05 17:04:51 +08:00 via iPhone
@Infernalzero 但事实是的确有很多人会这样写。。。我们公司的老项目 随处可见这样的 sql 。。虽然三天两头的把 Oracle 都得跑挂。
|
 |
22
ddou 2016-10-05 17:38:59 +08:00 via Android
直接 fire 吧
|
 |
23
jarlyyn 2016-10-05 17:42:38 +08:00 via Android
前端写 SQL ，还能 insert ？？？
|
 |
24
jhdxr 2016-10-05 18:06:44 +08:00 2
@ebony0319 你需要 navicat （的自动格式化
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 。。。（字数限制，以下省略。。。铜币在燃烧。。。 |
 |
26
akira 2016-10-05 18:52:43 +08:00
没有可读性的代码一般不敢用
|
 |
27
Smilecc 2016-10-05 19:12:43 +08:00
@ebony0319 navicat 一个 SQL 客户端 可以连各种数据库 里面有个 SQL 美化功能 点一下 就成这个样子啦
|
 |
28
falcon05 2016-10-05 20:14:49 +08:00 via iPhone
真乃神人也
|
 |
30
blacklee 2016-10-05 21:30:51 +08:00
活久见
这个词我几年以前就看过了，一直觉得这么严重的形容词我应该不会拿出来用的，直到今天…… |
 |
31
fhefh 2016-10-05 22:02:25 +08:00
这么复杂的查询语句 怎么写出来的（机器生成的也可以） 求方法~
|
|
32
ebony0319 OP @fhefh 有三种方法。都写伪代码。
第一种是 insert into table select * from table1 第二张是 insert into a from table a insert into table2 b 第三张在 sql server 的： select * into table from table2 第二种用得多一些。上面写这么多是因为有一个 ordersm 和 orderno 可以连起所有的表。然后要什么数据去什么表去取。 |
 |
33
billlee 2016-10-05 23:23:27 +08:00
前端传 SQL? 你听说过 SQL 注入吗？
|
 |
34
shenqi 2016-10-05 23:39:56 +08:00
怎么看都是是 orm 自己弄得 sql 啊。我就不信是手写的。
|
 |
35
lwbjing 2016-10-06 04:20:03 +08:00
为啥前端要干这事情...
|
 |
36
zhouzm 2016-10-06 08:15:10 +08:00
能写出这么复杂 sql 的人 sql 水平相当可以啊，为什么要伪装成一个前端？
|
 |
37
thinkif 2016-10-06 08:56:53 +08:00
有很多很多人在写代码的时候有很惰性的心理，总想在一个地方把事情处理了，所以就会出现把很复杂的操作扔到 SQL 中。
写这样的 SQL 不难，特别是很多刚入行的人，经常就会写出这样的，你要是一点一点读的话，会发现它就是把各种判断和运算放进去而已，就像平铺直叙的作文一样。 这样的结果会给自己挖个很大很大的坑，如果逻辑发生变化了，变动会非常困难。 而且更可怕的是过于依赖写死的内容，甚至传说中的 magic number ，一旦出现未知的数据很可能就崩了。。。 |
|
38
ebony0319 OP @thinkif 对。这里面其实语法是没有问题的，但是在一个联结的时候有一个表出现了认为的错误，所以导致整个结果的为空，但是要定位到这个错误非常困难。
|
 |
39
zhangv 2016-10-06 11:43:56 +08:00
谁都可能写出这种 SQL 吧，前端这枪躺的。。
|
 |
40
sutra 2016-10-06 17:25:40 +08:00
先用工具格式化一下就好了。
|
 |
41
forestyuan 2016-10-07 14:22:38 +08:00
前端直接操作数据库，貌似没有后端什么事了。楼主是 DBA ？
|
 |
42
jsjjdzg 2017-03-05 01:50:51 +08:00
这个应该是生成的吧，人真的能写这么长的 SQL 吗？（不是怀疑能力）
|
Select Language