有条件的ITAB分组都在SQL中具有子句

Question

这是这个问题的后续问题，但是我不想根据某种条件来处理组，并且无法为此找到适当的语法。我需要排除包含状态的文档"删除"的组，如果该组的至少一个成员具有此状态。

我尝试了到目前为止 GROUP...WITHOUT MEMBERS， LOOP...FOR GROUPS， REDUCE，这是我最终以

的方式解决的解决方案

DATA(lt_valid_doc) = VALUE tt_struct( 
      FOR ls_valid IN VALUE tt_struct( 
             FOR GROUPS <group_key> OF <wa> IN lt_ilot
             GROUP BY ( guid = <wa>-guid guid2 = <wa>-guid2 ) ASCENDING
             LET not_deleted = REDUCE #( INIT valid TYPE t_ref_s_struct
                                         FOR <m> IN GROUP <group_key>
                                         NEXT valid = COND #( 
                                               WHEN valid IS NOT BOUND OR <m>-stat = 'I1040' 
                                               THEN REF #( <m> ) ELSE valid ) )
             IN ( not_deleted->* ) )
      WHERE ( status NE 'I1040' ) 
      ( ls_valid ) ).

但是，这个解决方案对我来说似乎是多余的(I1040滤波器指示了两次(。是否有任何语法可以在一个语句(REDUCE， GROUP或erthing(中进行此操作，而无需在即时构造嵌套表并像我现在一样对其进行过滤？

如果我在上述所有语句(GROUP...WITHOUT MEMBERS，LOOP...FOR GROUPS和REDUCE(上使用WHERE条件，则仅过滤 base 行以分组而不是组本身。我需要与SQL中的HAVING相似。

UPDATE 好的，这是基于BSEG表的现实汇编示例。该任务仅找到未修复的文档，即将所有反向(xnegp = true(行的文档排除。

TYPES: BEGIN OF t_s_bseg,
         bukrs  TYPE bseg-bukrs,
         belnr  TYPE bseg-belnr,
         gjahr  TYPE bseg-gjahr,
         buzei  TYPE bseg-buzei,
         xnegp  TYPE bseg-xnegp,
       END OF t_s_bseg,
       tt_bseg TYPE SORTED TABLE OF t_s_bseg WITH EMPTY KEY.
TYPES: t_ref_s_bseg TYPE REF TO t_s_bseg.
DATA(lt_valid_fi_doc) = VALUE tt_bseg( 
  FOR ls_valid IN VALUE tt_bseg( 
         FOR GROUPS <group_key> OF <wa> IN lt_bseg
         GROUP BY ( bukrs = <wa>-bukrs belnr = <wa>-belnr gjahr = <wa>-belnr ) ASCENDING
         LET not_reversed = REDUCE #( INIT valid TYPE t_ref_s_bseg
                                     FOR <m> IN GROUP <group_key>
                                     NEXT valid = COND #( 
                                           WHEN valid IS NOT BOUND OR <m>-xnegp = abap_true 
                                           THEN REF #( <m> ) ELSE valid ) )
         IN ( not_reversed->* ) )
  WHERE ( xnegp NE abap_true ) 
  ( ls_valid ) ).

输入线

bukrs belnr       gjahr buzei xnegp
1000  0100000001  2019  1 
1000  0100000001  2019  2
1000  0100000003  2019  1
1000  0100000003  2019  2
1000  0100000004  2019  1
1000  0100000004  2019  2     X

doc 0100000004有反向线，因此结果应为

bukrs belnr       gjahr buzei xnegp
1000  0100000001  2019   
1000  0100000003  2019

Answer 1

这是一个不重复选择的解决方案，但仍然存在一个问题，这确实是"更好"？

如果一组线包含一条线，则解决方案是基于生成一个空行的，而不是保持不需要的线路。我不确定，但是也许另一个类似的解决方案可以保留对线路(not_deleted(的参考，并添加一个辅助变量以了解该参考是否要保留。我发现使用表索引(INDEX INTO(更加直观，但是如果tt_struct是剧本表类型。

。

我为代码提供ABAP单元测试，以便您可以快速尝试。

CLASS ltc_main DEFINITION FOR TESTING
      DURATION SHORT RISK LEVEL HARMLESS.
  PRIVATE SECTION.
    METHODS test FOR TESTING.
    METHODS cut.
    TYPES : BEGIN OF ty_struct,
              guid TYPE string,
              stat TYPE string,
            END OF ty_struct,
            tt_struct      TYPE STANDARD TABLE OF ty_struct WITH EMPTY KEY,
            t_ref_s_struct TYPE REF TO ty_struct.
    DATA: lt_ilot      TYPE tt_struct,
          lt_valid_doc TYPE tt_struct.
ENDCLASS.
CLASS ltc_main IMPLEMENTATION.
  METHOD cut.
    lt_valid_doc = VALUE #(
          FOR ls_valid IN VALUE tt_struct(
                 FOR GROUPS <group_key> OF <wa> IN lt_ilot
                 GROUP BY ( guid = <wa>-guid ) ASCENDING
                 LET x1 = REDUCE #(
                        INIT x2 = 0
                        FOR <m> IN GROUP <group_key> INDEX INTO x3
                        NEXT x2 = COND #(
                              WHEN <m>-stat = 'I1040' THEN -1
                              ELSE COND #( WHEN x2 <> 0 THEN x2 ELSE x3 ) ) )
                 IN ( COND #( WHEN x1 <> -1 THEN lt_ilot[ x1 ] ) ) )
          WHERE ( table_line IS NOT INITIAL )
          ( ls_valid ) ).
  ENDMETHOD.
  METHOD test.
    lt_ilot = VALUE #(
        ( guid = 'A' stat = 'I1000' )
        ( guid = 'A' stat = 'I1040' )
        ( guid = 'B' stat = 'I1020' )
        ( guid = 'C' stat = 'I1040' )
        ( guid = 'D' stat = 'I1040' )
        ( guid = 'D' stat = 'I1000' ) ).
    cut( ).
    cl_abap_unit_assert=>assert_equals( act = lt_valid_doc
          exp = VALUE tt_struct( ( guid = 'B' stat = 'I1020' ) ) ).
  ENDMETHOD.
ENDCLASS.

Answer 2

我真的希望那是某种个人测试案例，并且您不会在生产性环境中使用此编码。如果我必须了解您只想在编码上实现的目标，我会恨您；(

轻松解决此问题的关键是对表进行排序，以便删除条件始终在要处理的组的第一行中：

解决方案1随着唯一列表的输出：

DATA: lt_bseg TYPE STANDARD TABLE OF t_s_bseg.
SORT lt_bseg BY belnr xnegp DESCENDING.
DELETE ADJACENT DUPLICATES FROM lt_bseg COMPARING belnr.
DELETE lt_bseg WHERE xnegp = abap_true.

解决方案2

随着非唯一列表的输出：

DATA: lt_bseg TYPE STANDARD TABLE OF t_s_bseg,
      lf_prev_belnr TYPE belnr,
      lf_delete TYPE char1.
SORT lt_bseg BY belnr xnegp DESCENDING.
LOOP AT lt_bseg ASSIGNING FIELD-SYMBOL(<ls_bseg>).
    IF <ls_bseg>-belnr <> lf_prev_belnr.
        lf_delete = <ls_bseg>-xnegp.
        lf_prev_belnr = <ls_bseg>-belnr.
    ENDIF.
    IF lf_delete = abap_true.
        DELETE lt_bseg.
    ENDIF.
ENDLOOP.

Answer 3

以下解决方案可能不是最漂亮的解决方案，而是简单的。如果一个成员符合条件，我比添加整个组，如果所有组都失败了，则我更容易删除整个组。只是一个主意。

TYPES : BEGIN OF ty_struct,
          guid TYPE string,
          stat TYPE string,
        END OF ty_struct,
        tt_struct TYPE STANDARD TABLE OF ty_struct WITH EMPTY KEY.

DATA(lt_ilot) = VALUE tt_struct(
    ( guid = 'A' stat = 'I1000' )
    ( guid = 'A' stat = 'I1040' )
    ( guid = 'B' stat = 'I1020' )
    ( guid = 'C' stat = 'I1040' )
    ( guid = 'D' stat = 'I1040' )
    ( guid = 'D' stat = 'I1000' )).
LOOP AT lt_ilot INTO DATA(ls_ilot) WHERE stat = 'I1040'.
  DELETE lt_ilot WHERE guid = ls_ilot-guid.
ENDLOOP.