用法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 ).