SAP opensql子查询的使用示例
用法1:WHERE NOT EXISTS 或 WHERE EXISTS
- WHERE EXISTS:查询结果包含任意一个子查询结果集则返回真;否则假
- 子查询:SELECT * FROM marc WHERE matnr = ‘10000312’ AND ( werks = ‘1001’ OR werks = ‘9001’ )
- 子查询:SELECT * FROM marc WHERE matnr = ‘10000312’ AND werks = ‘9001’
- 子查询:SELECT * FROM marc WHERE matnr = ‘10000312’ AND ( werks = ‘1001’ OR werks = ‘9001’ )
- WHERE NOT EXISTS:查询结果包含任意一个子查询结果集则返回假;否则真
子查询:SELECT * FROM marc WHERE matnr = ‘10000312’ AND ( werks = ‘1001’ OR werks = ‘9001’ )
子查询:SELECT * FROM marc WHERE matnr = ‘10000312’ AND werks = ‘9001’
"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 ).
post SAP-Garson
原文链接:https://blog.csdn.net/qq_30797051/article/details/123563831文章来自于网络,如果侵犯了您的权益,请联系站长删除!