@
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.idINNER JOIN product cc ON a.productid =
cc.idLEFT 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.idINNER JOIN ORDER c ON a.orderno = c.orderno
AND a.ordersn = c.ordersn
INNER JOIN product cc ON c.productid =
cc.idINNER 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.idAND p.productno NOT LIKE 'P%'
LEFT OUTER JOIN gyname b ON a.gyid =
b.idINNER 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
。。。（字数限制，以下省略。。。铜币在燃烧。。。
Select Language