SAP opensql子查询的使用示例（WHERE NOT EXISTS 或 WHERE EXISTS）

用法1：WHERE NOT EXISTS 或 WHERE EXISTS

• WHERE EXISTS：查询结果包含任意一个子查询结果集则返回真；否则假
  • 子查询：

    SELECT *
      FROM marc AS a
      INTO TABLE lt_data
      WHERE EXISTS ( SELECT * FROM marc WHERE matnr = '10000312' AND ( werks = '1001' OR werks = '9001' ) )
      AND matnr = '10000312'
      ORDER BY matnr,werks
      INTO TABLE @DATA(lt_marc).
    

    
  • 子查询：

    SELECT *
      FROM marc AS a
      INTO TABLE lt_data
      WHERE EXISTS ( SELECT * FROM marc WHERE matnr = '10000312' AND werks = '9001' )
      AND matnr = '10000312'
      ORDER BY matnr,werks
      INTO TABLE @DATA(lt_marc).
    

    
• WHERE NOT EXISTS：查询结果包含任意一个子查询结果集则返回假；否则真

  • 子查询：

    SELECT *
      FROM marc AS a
      INTO TABLE lt_data
      WHERE NOT EXISTS ( SELECT * FROM marc WHERE matnr = '10000312' AND ( werks = '1001' OR werks = '9001' ) )
      AND matnr = '10000312'
      ORDER BY matnr,werks
      INTO TABLE @DATA(lt_marc).
    

    

  • 子查询：

    SELECT *
      FROM marc AS a
      INTO TABLE lt_data
      WHERE NOT EXISTS ( SELECT * FROM marc WHERE matnr = '10000312' AND werks = '9001' )
      AND matnr = '10000312'
      ORDER BY matnr,werks
      INTO TABLE @DATA(lt_marc).
    

    

    "WHERE EXISTS
    SELECT *
      FROM marc AS a
      WHERE EXISTS ( SELECT * FROM marc WHERE matnr = '10000312' AND ( werks = '1001' OR werks = '9001' ) )
      AND matnr = '10000312'
      ORDER BY matnr,werks
      INTO TABLE @DATA(lt_marc).
    
    cl_demo_output=>display( lt_marc ).
    
    "WHERE NOT EXISTS
    SELECT *
      FROM marc AS a
      WHERE NOT EXISTS ( SELECT * FROM marc WHERE matnr = '10000312' AND ( werks = '1001' OR werks = '9001' ) )
      AND matnr = '10000312'
      ORDER BY matnr,werks
      INTO TABLE @DATA(lt_marc1).
    
    cl_demo_output=>display( lt_marc1 ).
    

用法2：WHERE子查询

• WHERE条件子查询
  

  SELECT *
    FROM marc AS a
    WHERE matnr IN ( SELECT matnr FROM mara WHERE matnr IN ( '000000500210003177','000000500210003178' ) )
    AND werks IN ( SELECT werks FROM marc WHERE matnr IN ( '000000500210003177','000000500210003178' ) )
    INTO TABLE @DATA(lt_marc).
  
  cl_demo_output=>display( lt_marc ).
  

用法3：WITH + AS（高级用法）

• WITH + AS语法可使用UNION等关键字，更灵活
  

  "WITH + 子查询语法
  WITH +tmp_tab_name AS ( SELECT matnr,werks
  FROM marc
  WHERE matnr = '10000312' )
  
  SELECT b~*
  FROM +tmp_tab_name AS a
  INNER JOIN marc AS b
    ON a~matnr = b~matnr AND a~werks = b~werks
  WHERE b~werks IN ( '1001','1003' )
  ORDER BY b~matnr,b~werks
  INTO TABLE @DATA(lt_marc).
  
  cl_demo_output=>display( lt_marc ).