mbewh取数中主键访问的感悟-优快云博客

本文链接：https://blog.youkuaiyun.com/lymkelly/article/details/6185589

对mbewh取表的尝试

DATA : BEGIN OF itab OCCURS 0,
matnr TYPE matnr,
bwkey TYPE bwkey,

        lfgja TYPE lfgja,
        LFMON type LFMON,
        stprs TYPE stprs,
        yearmonth(6),

END OF itab.

data itab_it like table of itab with header line .

* data mbew_it like table of itab with header line.

DATA : BEGIN OF mbew_it OCCURS 0,
matnr TYPE matnr,
bwkey TYPE bwkey,

*        lfgja TYPE lfgja,
*        LFMON type LFMON,
*        stprs TYPE stprs,
*        yearmonth(6),

END OF mbew_it.

data itab1 like table of itab with header line.

* data:ym(6) .
PARAMETERS: ym(6) DEFAULT '201006'.
parameters:p_bukrs type bukrs obligatory.
*PARAMETERS: p_matnr TYPE matnr DEFAULT '1B01602BCN1W0'."'1TD26C1TCN0B'.
*SELECT * INTO CORRESPONDING FIELDS OF TABLE itab FROM mbewh WHERE
*matnr = p_matnr AND bwkey = '2101'.

SELECT * INTO CORRESPONDING FIELDS OF TABLE itab_it FROM mbew WHERE
bwkey = p_bukrs.

*mbew_it[] = itab_it[].

SELECT matnr
bwkey

        bwtar
        lfgja
        LFMON
        stprs
appending CORRESPONDING FIELDS OF TABLE itab_it FROM mbewh WHERE
bwkey = p_bukrs.
loop at itab_it.
mbew_it-matnr = itab_it-matnr.
mbew_it-bwkey = itab_it-bwkey .
collect mbew_it.
endloop.

loop at mbew_it.
clear itab.clear itab[].
loop at itab_it where matnr = mbew_it-matnr.
itab_it-yearmonth = itab-lfgja .
itab_it-yearmonth+4(2) = itab-lfmon .

if itab_it-yearmonth > ym.

else.
append itab_it to itab.
endif.
endloop.

sort itab by yearmonth descending.

read table itab index 1.
itab-yearmonth = ym.
append itab to itab1.

endloop.

时间比较长取数

其实上面这个方案是想到如果用物料去取的话会耗时很多（因为我原本的是验证好的单物料的程序很好用的就是集中取数时候效率问题）

所以费劲的想一起取出来（不用物料判断），现在看数据库耗时太多还是采取物料的方式吧

结果很惊人！

select matnr
bwkey

        bwtar
        lfgja
        lfmon
        stprs into corresponding fields of table itab_it from mbew where
bwkey = p_bukrs.

*mbew_it[] = itab_it[].
loop at itab_it.
clear itab.clear itab[].
select matnr
bwkey

        bwtar
        lfgja
        lfmon
        stprs
appending corresponding fields of table itab from mbewh where
matnr = itab_it-matnr and bwkey = p_bukrs.
append itab_it to itab.

loop at itab where matnr = itab_it-matnr.
itab-yearmonth = itab-lfgja .
itab-yearmonth+4(2) = itab-lfmon .

if itab-yearmonth > ym.
delete itab.

else.
modify itab.
endif.
endloop.

sort itab by yearmonth descending.

read table itab index 1.
itab-yearmonth = ym.
append itab to itab1.

endloop.

这也说明了主键访问的重要性！！！！！！！！！！！！！！！！！！

顺便说一下以上是个很好的算法求历史价格的保存呵呵

单个物料的完整程序如下：

*&---------------------------------------------------------------------*
*& Report ZMBEWH                                                      *
*&                                                                     *
*&---------------------------------------------------------------------*
*&                                                                     *
*&                                                                     *
*&---------------------------------------------------------------------*

report zmbewh .

data : begin of itab occurs 0,
matnr type matnr,
bwkey type bwkey,

        lfgja type lfgja,
        lfmon type lfmon,
        stprs type stprs,
        yearmonth(6),

end of itab.

* data:ym(6) .
parameters: ym(6) default '201006',
p_bukrs type bukrs obligatory.

parameters: p_matnr type matnr default '1B01602BCN1W0'."'1TD26C1TCN0B'.
*SELECT * INTO CORRESPONDING FIELDS OF TABLE itab FROM mbewh WHERE
*matnr = p_matnr AND bwkey = '2101'.

select matnr
bwkey

        bwtar
        lfgja
        lfmon
        stprs
into corresponding fields of table itab from mbewh where
matnr = p_matnr and bwkey = p_bukrs.

select * appending corresponding fields of table itab from mbew where
matnr = p_matnr and bwkey = p_bukrs.

data num type i.

num = lines( itab ).
loop at itab.
itab-yearmonth = itab-lfgja .
itab-yearmonth+4(2) = itab-lfmon .

if itab-yearmonth > ym." and sy-tabix <> num.
delete itab.
else.
modify itab.
endif.
endloop.

sort itab by yearmonth descending.

read table itab index 1.

write: itab-yearmonth, itab-stprs.

全公司的如下

report zmbewhnt .
define add_field.
wa_field-fieldname = &1.
wa_field-reptext_ddic = &2.
append wa_field to it_field.
end-of-definition.

type-pools slis.
data: gs_layout type slis_layout_alv.
data: g_repid type sy-repid.
data: it_field type slis_t_fieldcat_alv.
data: wa_field type slis_fieldcat_alv.

data : begin of itab occurs 0,
matnr type matnr,
bwkey type bwkey,

        lfgja type lfgja,
        lfmon type lfmon,
        stprs type stprs,
        yearmonth(6),

end of itab.

data itab_it like table of itab with header line .

* data mbew_it like table of itab with header line.

data : begin of mbew_it occurs 0,
matnr type matnr,
bwkey type bwkey,

*        lfgja TYPE lfgja,
*        LFMON type LFMON,
*        stprs TYPE stprs,
*        yearmonth(6),

end of mbew_it.

data itab1 like table of itab with header line.

* data:ym(6) .
parameters: ym(6) default '201006'.
parameters:p_bukrs type bukrs obligatory.
*PARAMETERS: p_matnr TYPE matnr DEFAULT '1B01602BCN1W0'."'1TD26C1TCN0B'.
*SELECT * INTO CORRESPONDING FIELDS OF TABLE itab FROM mbewh WHERE
*matnr = p_matnr AND bwkey = '2101'.

select matnr
bwkey

        bwtar
        lfgja
        lfmon
        stprs into corresponding fields of table itab_it from mbew where
bwkey = p_bukrs.

*mbew_it[] = itab_it[].
loop at itab_it.
clear itab.clear itab[].
select matnr
bwkey

        bwtar
        lfgja
        lfmon
        stprs
appending corresponding fields of table itab from mbewh where
matnr = itab_it-matnr and bwkey = p_bukrs.
append itab_it to itab.

loop at itab where matnr = itab_it-matnr.
itab-yearmonth = itab-lfgja .
itab-yearmonth+4(2) = itab-lfmon .

if itab-yearmonth > ym.
delete itab.

else.
modify itab.
endif.
endloop.

sort itab by yearmonth descending.

read table itab index 1.
itab-yearmonth = ym.
append itab to itab1.

endloop.

*loop at itab_it.
*mbew_it-matnr = itab_it-matnr.
*mbew_it-bwkey = itab_it-bwkey .
*collect mbew_it.
*endloop.

*loop at mbew_it.
*clear itab.clear itab[].
*loop at itab_it where matnr = mbew_it-matnr.
*itab_it-yearmonth = itab-lfgja .
*itab_it-yearmonth+4(2) = itab-lfmon .
*
*if itab_it-yearmonth > ym.
*
*else.
*append itab_it to itab.
*endif.
*endloop.

*
*sort itab_it by matnr yearmonth.

sort itab by yearmonth descending.

*read table itab index 1.
*itab-yearmonth = ym.
*append itab to itab1.
*
*
*endloop.
perform out.

*loop at itab.
*itab-yearmonth = itab-lfgja .
*itab-yearmonth+4(2) = itab-lfmon .
*
*if itab-yearmonth > ym." and sy-tabix <> num.
*delete itab.
*else.
*modify itab.
*endif.
*endloop.
*
*sort itab by yearmonth descending.
*
*read table itab index 1.

*write: itab-yearmonth, itab-stprs.

form out.

g_repid = sy-repid.
*gs_layout-colwidth_optimize = 'X'.
*gs_layout-box_fieldname = 'SEL'.
data: it_sort type slis_t_sortinfo_alv,
      ls_sort type slis_sortinfo_alv.
*ls_sort-fieldname = 'VBELN'.
*ls_sort-spos      = 1.
*ls_sort-up        = 'X'.
*ls_sort-subtot    = 'X'.
*APPEND ls_sort TO it_sort.
*it_sort-fieldname = 'VBELN'.
*it_sort-up = 'X'.
*append it_sort.
add_field 'MATNR' '物料'.
add_field 'BWKEY' '公司'.
add_field 'LFGJA' '年度'.
add_field 'LFMON' '表中月'.
add_field 'STPRS' '价格'.
add_field 'YEARMONTH' '查询年月'.

call function 'REUSE_ALV_GRID_DISPLAY' "输出退换货清单
         exporting
              i_callback_program = g_repid
              it_fieldcat        = it_field
              is_layout          = gs_layout
*              it_sort            = it_sort
              i_save             = 'A'
         tables
              t_outtab           = itab1.
endform.

顺便说下本来是想发布对mbewh的算法呵呵后来在速度上遇到麻烦速度成主角了

这个算法比较可取

取mbewh和mbew 剔除比输入月份大的数据，按照年月的字符串大到小排序，取第一个即可效率也挺高的

单个的直接取的，某个公司的也只需要半分多钟

mbewh取数中 主键访问的感悟

mbewh取数中主键访问的感悟