◆◆0

SQL语句中EXISTS子查询(subquery)使用方法以及效率分析

石头 7月 5, 2019 性能优化 19,114 views 0

1，语法

abap语言中的SQL支持子查询(subquery)，也就是在主查询SQL的where条件中再使用一个select查询语句对主查询的结果进行过滤。

语法：

语法

... EXISTS subquery ... 

效果 

This expression can be created using any subquery. It is true if the results set of the subquery
contains at least one row.

语法

... EXISTS subquery ...

效果

This expression can be created using any subquery. It is true if the results set of the subquery

contains at least one row.

2，效率

先说结果吧，exists效率还不错，可以用一下的。

之前都是使用for all entries，印象里SQL语句where中写一些“奇奇怪怪”的东西的效率都不会太高，现在简单写两个程序测试一下，

程序1，使用exists抽取被冲销的物料凭证，代码：

REPORT ytest_exist_sql.

DATA:lt_mseg TYPE STANDARD TABLE OF mseg.
SELECT *
  FROM mseg
  INTO TABLE lt_mseg
 WHERE EXISTS ( SELECT * FROM m_mbmps WHERE smbln = mseg~mblnr
                                        AND sjahr = mseg~mjahr
                                        AND smblp = mseg~zeile ).

REPORT ytest_exist_sql.

DATA:lt_mseg TYPE STANDARD TABLE OF mseg.

SELECT *

FROM mseg

INTO TABLE lt_mseg

WHERE EXISTS ( SELECT * FROM m_mbmps WHERE smbln = mseg~mblnr

AND sjahr = mseg~mjahr

AND smblp = mseg~zeile ).

程序2，不使用exists，而是用for all entries代替，代码：

REPORT ytest_exist_sql2.

DATA:lt_mseg   TYPE STANDARD TABLE OF mseg WITH HEADER LINE,
     lt_mseg_2 TYPE STANDARD TABLE OF mseg WITH HEADER LINE.

SELECT *
  FROM mseg
  INTO TABLE lt_mseg
  .
SELECT *
  INTO TABLE lt_mseg_2
  FROM mseg
   FOR ALL ENTRIES IN lt_mseg
 WHERE smbln = lt_mseg-mblnr
   AND sjahr = lt_mseg-mjahr
   AND smblp = lt_mseg-zeile.