mysql - 优化此查询

Question

有3个表的结构

Table min consist of matcode,min_qty,jo_no,mr_no
Table min_out_body consist of matcode,out_qty,jo_no,mr_no
Table eu_min_out_body consist of matcode,out_qty,jo_no,mr_no

数据如下:

[min]
matcode       min_qty           jo_no           mr_no
xxx           100               1A              A11
xxx           150               2A              A22

[min_out_body]
matcode       out_qty           jo_no           mr_no
xxx           10                1A              A11
xxx           60                1A              A11
xxx           100               2A              A22

[eu_min_out_body]
matcode       out_qty           jo_no           mr_no
xxx           20                1A              A11
xxx           50                2A              A22

我想要达到的目标是:

matcode        min_qty          jo_no           mr_no          balance
xxx            100              1A              A11            10
xxx            150              2A              A22            0

使用以下代码查询:

SELECT
    min.matcode,
    min.min_qty,
    min.jo_no,
    min.mr_no
    (min.min_qty-(
        select ifnull(sum(out_qty),0) 
        FROM min_out_body 
        WHERE matcode=min.matcode 
        and jo_no=min.jo_no 
        and mr_no=min.mr_no
        )-(
        select ifnull(sum(out_qty),0) 
        FROM eu_min_out_body 
        WHERE matcode=min.matcode 
        and jo_no=min.jo_no 
        and mr_no=min.mr_no
        )
    ) as balance
FROM min
WHERE min.matcode = 'xxx'
    and (min.min_qty - (select
              ifnull(sum(out_qty),0)
            FROM min_out_body
            WHERE matcode = min.matcode
                and jo_no = min.jo_no
                and mr_no = min.mr_no) - (select
                            ifnull(sum(out_qty),0)
                              FROM eu_min_out_body
                              WHERE matcode = min.matcode
                              and jo_no = min.jo_no
                              and mr_no = min.mr_no)) > 0

我可以得到结果，但有什么方法可以简化查询并减少处理时间吗？

Answer 1

我看到两个选项，并且根据您处理的数据量可能会比其他选项执行得更好...第一个更易于阅读，但简化了 WHERE 子句并且可能是您想要运行的内容..

包装您的查询，但仅限于 WHERE MAT_CODE 限定符。让那个“PQ”(PreQuery)返回一切——所有余额。然后，只需应用 WHERE balance > 0 与在 where 子句中再次完成的复杂余额计算。

SELECT PQ.*
   from
      ( SELECT
            min.matcode,
            min.min_qty,
            min.jo_no,
            min.mr_no
            (min.min_qty 
              - ( select ifnull(sum(out_qty),0) 
                    FROM min_out_body 
                   WHERE min_no = min.min_no 
                     and matcode = min.matcode 
                     and jo_no = min.jo_no 
                     and mr_no = min.mr_no )
              - ( select ifnull(sum(out_qty),0) 
                    FROM eu_min_out_body 
                   WHERE min_no=min.min_no 
                     and matcode=min.matcode 
                     and jo_no=min.jo_no 
                     and mr_no=min.mr_no ) ) as balance
           FROM 
              min
           WHERE 
                  min.matcode = 'xxx' ) PQ
   where
      balance > 0

如果数据集更大，它看起来会更大一些，因为它会根据您的内部字段选择正在处理的组件对每个“out_body”表和组进行一次预聚合查询。然后 LEFT JOIN 到那些表。这种方法的好处是它不会在每个记录的基础上运行内部 select sum() ，如果你有 1000 条记录要经过，可能会为你节省更多的时间......特别是如果它正在进行通过 MIN_OUT_BODY 和 EU_MIN_OUT_BODY。

通过 Left-Join，无论是他们还是不是他们，IFNULL() 的工作方式相同，然后 WHERE 子句是相同的简单可读的余额计算，而无需再次重新查询。

SELECT
      min.matcode,
      min.min_qty,
      min.jo_no,
      min.mr_no,
      min.min_qty - ifnull( MOB1.out_qty, 0 ) - ifnull( EMOB1.out_qty, 0 ) balance
   from
      min
         LEFT JOIN ( select 
                      mob.matcode,
                      mob.min_no,
                      mob.jo_no,
                      mob.mr_no,
                      SUM( mob.out_qty ) out_qty
                   from
                      min_out_body mob
                   where
                      mob.matcode = 'xxx'
                   group by
                      mob.matcode,
                      mob.min_no,
                      mob.jo_no,
                      mob.mr_no ) MOB1
            ON min.matcode = MOB1.matcode
            AND min.min_no = MOB1.min_no
            AND min.jo_no = MOB1.jo_no
            AND min.mr_no = MOB1.mr_no 

         LEFT JOIN ( select 
                      Emob.matcode,
                      Emob.min_no,
                      Emob.jo_no,
                      Emob.mr_no,
                      SUM( Emob.out_qty ) out_qty
                   from
                      eu_min_out_body Emob
                   where
                      Emob.matcode = 'xxx'
                   group by
                      Emob.matcode,
                      Emob.min_no,
                      Emob.jo_no,
                      Emob.mr_no ) EMOB1
            ON min.matcode = EMOB1.matcode
            AND min.min_no = EMOB1.min_no
            AND min.jo_no = EMOB1.jo_no
            AND min.mr_no = EMOB1.mr_no 
   where
          min.matcode = 'xxx'
      AND min.min_qty - ifnull( MOB1.out_qty, 0 ) - ifnull( EMOB1.out_qty, 0 ) > 0