mysql - 在mysql中用join替换子查询

Question

我有一个 mysql 查询，例如...

SELECT OrderTransaction.buyer, OrderTransaction.parent_id
FROM  order_transactions as OrderTransaction
INNER JOIN (
    SELECT buyer
    FROM  order_transactions as dy
    left join orders as ebay on ebay.id=dy.parent_id
    where ebay.status='0' and dy.parent_id IN (
        SELECT parent_id
        FROM  order_shipping_details as ds
        left join orders as ebays on ebays.id=ds.parent_id
        where ebays.status='0'  and ebays.combined=0
        GROUP BY ds.Street
        HAVING count(ds.id) > 1
    ) and ebay.combined=0
    group by dy.buyer
) dup ON dup.buyer=OrderTransaction.buyer 
left join orders as ebay on ebay.id=OrderTransaction.parent_id
where ebay.market_type!='shopclue' and ebay.status='0' and ebay.combined=0

我需要优化此查询并希望删除带有连接的内部选择。任何帮助，将不胜感激。提前致谢。

Answer 1

试试下面这段代码可能比你目前使用的代码运行得更快:

DROP TEMPORARY TABLE IF EXISTS temp1;
CREATE TEMPORARY TABLE temp1;

SELECT buyer

FROM  order_transactions AS dy

LEFT JOIN orders AS ebay ON ebay.id=dy.parent_id

WHERE ebay.status='0' 
AND dy.parent_id 
IN (
SELECT parent_id
FROM  order_shipping_details AS ds
left join orders AS ebays ON ebays.id=ds.parent_id
where ebays.status='0'  and ebays.combined=0
GROUP BY ds.Street
HAVING count(ds.id) > 1) 
AND ebay.combined= '0'
;

SELECT 

OrderTransaction.buyer, 
OrderTransaction.parent_id

FROM  order_transactions AS OrderTransaction
INNER JOIN temp1 AS tmp ON tmp.buyer = OrderTransaction.buyer
LEFT JOIN orders AS ebay ON ebay.id = OrderTransaction.parent_id

WHERE ebay.market_type! = 'shopclub' AND ebay.status = '0' and ebay.combined = '0'


Please let me know if you have any questions!