如何使用一条ABAP SQL语句连接以下三个表

我发现它更容易使用 UNION ,第一个选择将返回与匹配其中一个用户角色(elias、VA01、SD_角色和SD2_角色)的事务对应的行,第二个选择将返回与不匹配任何用户角色(elias、VF01)的事务对应的行。

我用USR07替换ZMYTABLE来测试它。

    SELECT usr07~bname, usr07~tcode, agr_1251~agr_name
      FROM agr_users
      INNER JOIN usr07
        ON usr07~bname EQ agr_users~uname
      INNER JOIN agr_1251
        ON agr_1251~agr_name EQ agr_users~agr_name
           AND agr_1251~low  EQ usr07~tcode
    UNION
    SELECT DISTINCT usr07~bname, usr07~tcode, ' ' AS agr_name
      FROM usr07
      WHERE NOT EXISTS (
        SELECT * FROM agr_users
          INNER JOIN agr_1251
            ON agr_1251~agr_name EQ agr_users~agr_name
          WHERE usr07~bname  EQ agr_users~uname
            AND agr_1251~low EQ usr07~tcode )
    INTO TABLE @DATA(result).

    SORT result BY bname tcode agr_name.
    TYPES ty_result LIKE result.
    assert_equals( act = result exp = VALUE ty_result(
      ( bname = 'elias' tcode = 'VA01' agr_name = 'SD2_role' )
      ( bname = 'elias' tcode = 'VA01' agr_name = 'SD_role'  )
      ( bname = 'elias' tcode = 'VF01' agr_name = ''         ) ) ).    

下面是ABAP单元测试代码,以演示它的工作原理,如果需要,您可以使用它。您需要ABAP7.52(开放式SQL测试双框架)。

CLASS ltc_main DEFINITION FOR TESTING DURATION SHORT RISK LEVEL HARMLESS
      INHERITING FROM cl_aunit_assert.
  PRIVATE SECTION.
    METHODS test FOR TESTING.
    CLASS-METHODS: class_setup, class_teardown.
    CLASS-DATA environment TYPE REF TO if_osql_test_environment.
ENDCLASS.
CLASS ltc_main IMPLEMENTATION.
  METHOD class_setup.
    environment = cl_osql_test_environment=>create( i_dependency_list = VALUE #( 
          ( 'USR07' ) ( 'AGR_1251' ) ( 'AGR_USERS' ) ) ).
  ENDMETHOD.
  METHOD test.
    TYPES ty_usr07 TYPE STANDARD TABLE OF usr07 WITH EMPTY KEY.
    TYPES ty_agr_1251 TYPE STANDARD TABLE OF agr_1251 WITH EMPTY KEY.
    TYPES ty_agr_users TYPE STANDARD TABLE OF agr_users WITH EMPTY KEY.

    environment->insert_test_data( EXPORTING i_data = VALUE ty_usr07(
      ( bname = 'elias' tcode = 'VA01' timestamp = 1 )
      ( bname = 'elias' tcode = 'VF01' timestamp = 2 ) ) ).
    environment->insert_test_data( EXPORTING i_data = VALUE ty_agr_1251(
      ( agr_name = 'SD_role'  low = 'VA01' counter = 1 )
      ( agr_name = 'SD2_role' low = 'VA01' counter = 1 )
      ( agr_name = 'SD3_role' low = 'VA01' counter = 1 )
      ( agr_name = 'SD_role ' low = 'VA02' counter = 2 )
      ( agr_name = 'FI_role ' low = 'VF01' counter = 1 )
      ( agr_name = 'FI_role ' low = 'VF02' counter = 2 ) ) ).
    environment->insert_test_data( EXPORTING i_data = VALUE ty_agr_users(
      ( uname = 'elias' agr_name = 'SD_role ' )
      ( uname = 'elias' agr_name = 'SD2_role' )
      ( uname = 'maria' agr_name = 'SD_role ' )
      ( uname = 'maria' agr_name = 'FI_role ' ) ) ).

    "<==== here insert the ABAP SQL provided above & expectations to verify

    ROLLBACK WORK.

  ENDMETHOD.

  METHOD class_teardown.
    environment->destroy( ).
  ENDMETHOD.

ENDCLASS.

如果您有ABAP版本<7.50, 协会 SELECT ,第一个 INTO TABLE @DATA(result) 第二个是带 APPENDING TABLE result

PS:受其他答案的启发,我也做了以下测试,它们不起作用(大多数测试将角色“FI_role”返回为“VF01”,而不是一个空角色)。

尝试1-A失败:

     SELECT usr07~bname, usr07~tcode, agr_1251~agr_name
       FROM agr_users
       INNER JOIN usr07
         ON usr07~bname EQ agr_users~uname
       INNER JOIN agr_1251
         ON agr_1251~agr_name EQ agr_users~agr_name AND
            agr_1251~low      EQ usr07~tcode
       INTO TABLE @DATA(result).
    SORT result BY bname tcode agr_name.
    TYPES ty_result LIKE result.
    assert_equals( act = result exp = VALUE ty_result(
      ( bname = 'elias' tcode = 'VA01' agr_name = 'SD2_role' )
      ( bname = 'elias' tcode = 'VA01' agr_name = 'SD_role' ) ) ).

尝试1-B失败:

     SELECT DISTINCT usr07~bname,
                 usr07~tcode,
                 agr_1251~agr_name
           FROM usr07
           INNER JOIN agr_1251
             ON agr_1251~low EQ usr07~tcode
           INNER JOIN agr_users
             ON agr_users~uname EQ usr07~bname
           WHERE
              agr_users~agr_name EQ agr_1251~agr_name OR EXISTS (
              SELECT *
              FROM agr_users AS inner_agr_users
              INNER JOIN agr_1251 AS inner_agr_1251
                ON inner_agr_1251~agr_name EQ inner_agr_users~agr_name
              WHERE
                inner_agr_users~agr_name EQ agr_1251~agr_name
           )
           INTO TABLE @DATA(result).
    SORT result BY bname tcode agr_name.
    TYPES ty_result LIKE result.
    assert_equals( act = result exp = VALUE ty_result(
      ( bname = 'elias' tcode = 'VA01' agr_name = 'SD2_role' )
      ( bname = 'elias' tcode = 'VA01' agr_name = 'SD_role' )
      ( bname = 'elias' tcode = 'VF01' agr_name = 'FI_role' ) ) ).

尝试2失败:

     SELECT b~bname, b~tcode, a~agr_name
       FROM agr_1251 as a
       INNER JOIN usr07 as b
         ON a~low EQ b~tcode
       INNER JOIN agr_users as c
         ON a~agr_name EQ c~agr_name
       INTO TABLE @DATA(result).
    SORT result BY bname tcode agr_name.
    TYPES ty_result LIKE result.
    assert_equals( act = result exp = VALUE ty_result(
      ( bname = 'elias' tcode = 'VA01' agr_name = 'SD2_role' )
      ( bname = 'elias' tcode = 'VA01' agr_name = 'SD_role' )
      ( bname = 'elias' tcode = 'VA01' agr_name = 'SD_role' )
      ( bname = 'elias' tcode = 'VF01' agr_name = 'FI_role' ) ) ).

要访问所有记录,应使用DB access,如下所示。

SELECT b~zuser, b~ztcode, a~agr_name
  FROM agr_1251 as a
  INNER JOIN zmytable as b
    ON a~tcode EQ b~tcode
  INNER JOIN agr_user as c
    ON a~agr_name EQ c~agr_name.

Rest可以使用ABAP显示输出。

数据库访问应该如下所示:

SELECT DISTINCT zmytable~zuser,
                zmytable~ztcode,
                agr_1251~agr_name
FROM zmytable
INNER JOIN agr_1251
  ON agr_1251.ztcode EQ zmytable.ztcode
INNER JOIN agr_users
  ON agr_users.zuser EQ zmytable.zuser
WHERE
   agr_users.agr_name EQ agr_1251.agr_name OR EXISTS(
   SELECT *
   FROM agr_users AS inner_agr_users
   INNER JOIN agr_1251 AS inner_agr_1251
     ON inner_agr_1251.agr_name EQ inner_agr_users.agr_name
   WHERE
     inner_agr_users.agr_name EQ agr_1251.agr_name
).

INTO 子句并处理输出。

• 我计划在有时间的时候用解释来更新答案。

适用于版本>7.4.

SELECT c~zuser, a~zrole, c~ztcode INTO CORRESPONDING FIELDS OF TABLE @lt_result
     FROM agr_users AS a INNER JOIN agr_1251 AS b 
         ON a~zrole = b~zrole
            RIGHT OUTER JOIN zmytable AS c 
              ON c~ztcode = b~ztcode AND c~zuser = a~zuser.