mysql - SQL中多个相互关联的循环

Loop1是通过一些简单的逻辑得出的。 Loop2是通过非常复杂的逻辑派生的。 Loop3非常简单，只需将Loop2加2即可得出

现在，我想通过Loop1，Loop2和Loop3的组合派生Loop4。问题在于，如果我再次在Loop3中派生逻辑，则loop2是一个非常繁琐的逻辑，并且查询运行非常缓慢。为了提供更多的清晰度，我正在使用loop1＆2查找loop3，并使用loop1,2＆3查找loop4。请提出一种使该查询工作的方法。

select sre.shipmentId,
       loop1.TRY1,
       loop2.TRY2,
       loop3.TRY3,
       (select case when u>0 then loop1.TRY1 when u>1 then loop2.TRY2 else loop3.TRY3 end) as loop4
from `shipmentRouteEvent` sre
left join (select sre1.shipmentId as s1, (case when .....>0 then .... end) ad TRY1
           from `shipmentRouteEvent` sre1
           where sre1.updateDate='2013-07-01'
          ) as loop1 on sre.id=try1.id
left join (select some heavy logic which will modify TRY1 to TRY2) as loop2
left join (select (TRY2+2) as TRY3) as loop3
where sre.updateDate='2013-07-01'

最佳答案

我认为您想将其作为嵌套子查询来执行：

from (select . . .
      fro (select . . .
           from `shipmentRouteEvent` sre left join
                 (select sre1.shipmentId as s1, (case when .....>0 then .... end) ad TRY1
                  from `shipmentRouteEvent` sre1
                  where sre1.updateDate='2013-07-01'
                ) loop1
                on sre.id=try1.id
           ) loop1 left join
           (select some heavy logic which will modify TRY1 to TRY2
           ) loop2
           on . . .
      ) loop2 left outer join
      (. . .
      ) loop3
      on . . .

嵌套的每个级别将允许您在下一个级别使用结果，因此不需要重新计算结果。