如何改进查询,因为它将超时异常。有人能帮我改进查询吗。。。???
UPDATE pdtdeliveryTable p SET p.OrderID = IFNULL
(
(SELECT OrderID from OrderTable o where o.DiagramID=p.DiagramID AND o.DeliveryDate=
(
Select min(o2.DeliveryDate) from OrderTable o2 where o2.DiagramId=o.DiagramID Order by o2.DeliveryDate
)Limit 1),0
)
where p.OrderID=0;
最佳答案
试试这个:
UPDATE pdtdeliveryTable p
LEFT OUTER JOIN (SELECT o.DiagramID, o.OrderID FROM OrderTable o
INNER JOIN ( SELECT DiagramID, MIN(DeliveryDate) DeliveryDate
FROM OrderTable GROUP BY DiagramID
) AS A ON o.DiagramID = A.DiagramID AND o.DeliveryDate = A.DeliveryDate
) AS A ON A.DiagramID = p.DiagramID
SET p.OrderID = IFNULL(A.OrderID, 0);
或者
UPDATE pdtdeliveryTable p
LEFT OUTER JOIN ( SELECT DiagramID, OrderID
FROM (SELECT DiagramID, OrderID, DeliveryDate FROM OrderTable ORDER BY DiagramID, DeliveryDate) AS A
GROUP BY DiagramID
) AS A ON p.DiagramID = A.DiagramID
SET p.OrderID = IFNULL(A.OrderID, 0);
关于mysql - 可以针对超时异常改进查询吗,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/24320612/