优化了查询 [英] Optimized the query

问题描述

具有 3 个表的结构

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

数据如下:

[min]
matcode       min_qty           jo_no           mr_no         min_no
xxx           100               1A              A11           A111
xxx           150               2A              A22           A222
yyy           100               1A              A11           A111
zzz           150               2A              A22           A222

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

[eu_min_out_body]
matcode       out_qty           jo_no           mr_no         min_no
xxx           20                1A              A11           A111
xxx           50                2A              A22           A222
zzz           100               2A              A22           A222

我想要达到的是有结果:

What i am trying to achieve is to have a result:

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

使用以下代码查询:

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

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

I can get the result, but is there any way to simplify the query and reduce the process time?

这是来自 Sql Fiddle 的示例数据 sqlfiddle.com/#!2/1fb8b/1

Here is sample data from Sql Fiddle sqlfiddle.com/#!2/1fb8b/1

推荐答案

我想我不太明白你在上一个几乎相同的问题中的评论.答案实际上应该是相同的，除了删除所有 WHERE 子句，其中它明确地只执行 matcode = 'xxx'.

I guess I didn't quite understand from your comment in the previous near identical question. The answer should actually be the same with exception of removing all the WHERE clauses where it was explicitly doing only the matcode = 'xxx'.

通过删除这些，您将获得所有 matcode 作为每个 min/eu_min 表的标准分组的一部分.

By removing those, you will get ALL matcode as part of the group by criteria for each min/eu_min tables.

删除 WHERE 子句.

Remove the WHERE clauses.

 where mob.matcode = 'xxx'

 where Emob.matcode = 'xxx'

 where min.matcode = 'xxx' AND min.min_qty ....
to just
 where min.min_qty ...

这篇关于优化了查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！