第一个 ：
SELECT
a.request_detail_id,
a.vendorid,
a.request_id,
a.s1,
a.s2,
a.s3,
a.s4,
a.profit,
a.ratio,
a.requestqty,
a.stockqty,
a.sponroadqty,
a.reqonroadqty,
a.reqpurqty,
a.piqty,
a.ispurchase,
a.remark,
a.skuid,
a.v_req_detail_id,
a.is_rollback_collection,
a.v_req_detail_id_sort,
a.reqdeliverydate,
b.sku,
b.description,
bi.purspec,
bi.purdesc,
a.purpkgid,
a.skulabel,
a.skulabel skulable,
a.purpkg,
a.stopkg,
a.purnote1,
a.purnote2,
-- IFNULL(sup.minqty ,- 1) AS minqty,
o. STATUS,
o.shipping_date,
SUM(
CASE
WHEN o.shipping_date >= '2015-06-15' THEN
d.quantity
ELSE
0
END
) AS quantityT,
SUM(
CASE
WHEN o.shipping_date >= '2015-05-16'
AND o.shipping_date < '2015-06-15' THEN
d.quantity
ELSE
0
END
) AS quantityS,
SUM(
CASE
WHEN o.shipping_date >= '2015-04-16'
AND o.shipping_date < '2015-05-16' THEN
d.quantity
ELSE
0
END
) AS quantityN
FROM
rs_request_detail a
LEFT JOIN rs_request rq ON rq.request_id = a.request_id
INNER JOIN bs_item_locale b ON a.skuid = b.skuid
AND b.lang_id = 1
INNER JOIN bs_item bi ON a.skuid = bi.skuid
LEFT JOIN (
SELECT
psi.skuid,
IFNULL(
MIN(
IF (
psi.minqty = 0,
999999999,
psi.minqty
)
) ,- 1
) AS minqty
FROM
pr_supplier_item psi
LEFT JOIN pr_supplier ps ON ps.supplierid = psi.supplierid
AND ps.inactive = 1
WHERE
psi.itemstatus = 4
GROUP BY
skuid
) AS sup ON sup.skuid = a.skuid
LEFT JOIN so_order_detail d ON d.skuid = b.skuid
LEFT JOIN so_order o ON o.order_id = d.order_id AND o. STATUS = 4 and o.shipping_date < '2015-05-16'
WHERE
(1 = 1)
AND a.request_id = 15259
AND b.lang_id = 1
GROUP BY
a.request_detail_id
ORDER BY
a.v_req_detail_id_sort DESC,
a.v_req_detail_id ASC,
a.reqpurqty DESC
LIMIT 0,100
SELECT
a.request_detail_id,
a.vendorid,
a.request_id,
a.s1,
a.s2,
a.s3,
a.s4,
a.profit,
a.ratio,
a.requestqty,
a.stockqty,
a.sponroadqty,
a.reqonroadqty,
a.reqpurqty,
a.piqty,
a.ispurchase,
a.remark,
a.skuid,
a.v_req_detail_id,
a.is_rollback_collection,
a.v_req_detail_id_sort,
a.reqdeliverydate,
b.sku,
b.description,
bi.purspec,
bi.purdesc,
a.purpkgid,
a.skulabel,
a.skulabel skulable,
a.purpkg,
a.stopkg,
a.purnote1,
a.purnote2,
IFNULL(sup.minqty ,- 1) AS minqty,
o. STATUS,
o.shipping_date,
SUM(
CASE
WHEN o.shipping_date >= '2015-06-15' THEN
d.quantity
ELSE
0
END
) AS quantityT,
SUM(
CASE
WHEN o.shipping_date >= '2015-05-16'
AND o.shipping_date < '2015-06-15' THEN
d.quantity
ELSE
0
END
) AS quantityS,
SUM(
CASE
WHEN o.shipping_date >= '2015-04-16'
AND o.shipping_date < '2015-05-16' THEN
d.quantity
ELSE
0
END
) AS quantityN
FROM
rs_request_detail a
LEFT JOIN rs_request rq ON rq.request_id = a.request_id
INNER JOIN bs_item_locale b ON a.skuid = b.skuid
AND b.lang_id = 1
INNER JOIN bs_item bi ON a.skuid = bi.skuid
LEFT JOIN so_order_detail d ON d.skuid = b.skuid
LEFT JOIN so_order o ON o.order_id = d.order_id AND o. STATUS = 4 and o.shipping_date < '2015-05-16'
LEFT JOIN (
SELECT
psi.skuid,
IFNULL(
MIN(
IF (
psi.minqty = 0,
999999999,
psi.minqty
)
) ,- 1
) AS minqty
FROM
pr_supplier_item psi
LEFT JOIN pr_supplier ps ON ps.supplierid = psi.supplierid
AND ps.inactive = 1
WHERE
psi.itemstatus = 4
GROUP BY
skuid
) AS sup ON sup.skuid = a.skuid
WHERE
(1 = 1)
AND a.request_id = 15259
AND b.lang_id = 1
GROUP BY
a.request_detail_id
ORDER BY
a.v_req_detail_id_sort DESC,
a.v_req_detail_id ASC,
a.reqpurqty DESC
LIMIT 0,100
