zjg448281779 于 2015.07.15 20:37 提问

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.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.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

3个回答

u012216727      2015.07.15 21:23

zjg448281779 有9999999那个子查询，关联在INNER JOIN bs_item bi ON a.skuid = bi.skuid后面就快，关联在其他地方都慢，这是为什么
2 年多之前 回复
danielinbiti      2015.07.15 21:54

zjg448281779 但是我把有999999的那个子查询放在主表的后面速度也很慢 只有放在INNER JOIN bs_item bi ON a.skuid = bi.skuid这后面才快
2 年多之前 回复
strutce      2015.07.16 00:03

LEFT JOIN 哪个在前哪个当主表来关联数据，前的全部显示，后的只显示有关联到的数据其他null显示