点击此处---> 群内免费提供SAP练习系统(在群公告中)
加入QQ群:457200227(SAP S4 HANA技术交流) 群内免费提供SAP练习系统(在群公告中)
这篇文章是ABAP 7.4 及更高版本 [6] :开放 SQL 增强第 1 部分的延续。在这篇文章中,您将了解 ABAP 7.40 版本之后引入的开放 SQL 增强功能。
Select with Joins
JOIN 一直存在,我们一直在避免它们,而是转而使用FOR ALL ENTRIES。现在不用了,我们现在可以通过 SAP HANA 有效地使用连接。我们现在也用 RIGHT OUTER JOIN。
Inner Join
以 ON 条件连接表。内连接意味着只有在两个表中都找到数据时才选择数据。
DATA(lv_city_from) = 'BERLIN'. SELECT c~carrname, p~connid FROM scarr AS c INNER JOIN spfli AS p ON p~carrid = c~carrid AND p~cityfrom = @lv_city_from INTO TABLE @DATA(lt_flights).
Left Outer Join
在这里,scarr
是左表,spfli
是右表。从左表中选择数据,如果在右表中找到匹配的记录,则填充右表中的字段,否则它们具有初始值。
DATA(lv_city_from) = 'BERLIN'. SELECT c~carrname, p~connid FROM scarr AS c LEFT OUTER JOIN spfli AS p ON p~carrid = c~carrid AND p~cityfrom = @lv_city_from INTO TABLE @DATA(lt_flights).
Right Outer Join
这里scarr
也是左表和spfli
右表。从右表中选择数据,如果在左表中找到匹配的记录,则填充左表中的字段,否则它们具有初始值。
DATA(lv_city_from) = 'BERLIN'. SELECT c~carrname, p~connid FROM scarr AS c RIGHT OUTER JOIN spfli AS p ON p~carrid = c~carrid AND p~cityfrom = @lv_city_from INTO TABLE @DATA(lt_flights).
Select all fields from a table in Join
现在,您可以使用关键字FIELD指定要选择的字段,还可以使用<tablename >-* 指示应选择该表中的所有字段。
SELECT FROM sflight INNER JOIN spfli ON sflight~carrid = spfli~carrid AND sflight~connid = spfli~connid FIELDS spfli~*, sflight~fldate INTO TABLE @DATA(result).
UNION in SELECT
UNION 创建了两个 SELECT 语句的结果集的并集。UNION 右边的SELECT 语句的结果集的行被插入到UNION 左边的SELECT 语句的结果集中。结果集的列保留在 UNION 左侧的 SELECT 语句中定义的名称。
OPEN SQL 从 7.50 开始提供 Union。
"Before 7.50 SELECT schedule~carrid, schedule~connid, schedule~fldate, flight~cityfrom, flight~cityto FROM sflight AS schedule INNER JOIN spfli AS flight ON schedule~carrid = flight~carrid AND schedule~connid = flight~connid WHERE flight~cityfrom = 'FRANKFURT' INTO TABLE @DATA(lt_flights). SELECT schedule~carrid, schedule~connid, schedule~fldate, flight~cityfrom, flight~cityto FROM sflight AS schedule INNER JOIN spfli AS flight ON schedule~carrid = flight~carrid AND schedule~connid = flight~connid WHERE flight~cityfrom = 'SAN FRANCISCO' APPENDING TABLE @lt_flights. "From 7.50 SELECT schedule~carrid, schedule~connid, schedule~fldate, flight~cityfrom, flight~cityto FROM sflight AS schedule INNER JOIN spfli AS flight ON schedule~carrid = flight~carrid AND schedule~connid = flight~connid WHERE flight~cityfrom = 'FRANKFURT' UNION ALL SELECT schedule~carrid, schedule~connid, schedule~fldate, flight~cityfrom, flight~cityto FROM sflight AS schedule INNER JOIN spfli AS flight ON schedule~carrid = flight~carrid AND schedule~connid = flight~connid WHERE flight~cityfrom = 'SAN FRANCISCO' INTO TABLE @DATA(lt_flights).
请注意 INTO 子句移动到末尾。因此,如果我们将子句移动到所有查询的结尾,那将是一个好习惯。另外,如果我们只想要唯一记录,我们可以使用 UNION DISTINCT 而不是 UNION ALL。(如果 UNION 之后没有指定任何内容,则使用的默认模式为 DISTINCT )。
重要:
只要选择相同的字段,Union 还可以从不同的表中选择数据。
UNION 查询不能使用 FOR ALL ENTRIES
AGGREGATE 可用于 UNION 查询
SELECT FROM INTERNAL_TABLE
从 7.52,您可以使用SQL查询内表。这是一个例子。
"Select from Database TablesSELECT schedule~carrid AS carrier_id, schedule~connid AS connection_id, schedule~fldate AS flight_date, flight~cityfrom AS city_from, flight~cityto AS city_to FROM sflight AS schedule INNER JOIN spfli AS flight ON schedule~carrid = flight~carrid AND schedule~connid = flight~connid INTO TABLE @DATA(lt_flights).
"Select from Internal TableSELECT carrier_id, connection_id, flight_date FROM @lt_flights AS flights WHERE city_from = 'FRANKFURT' INTO TABLE @DATA(lt_new_flights).
当我们从内部表中选择时,我们需要对主机变量使用转义字符@,并使用 AS 提及别名。
FIELD Keyword
查询中的字段列表也可以用关键字 FIELD 指定。这允许您在 FROM 子句之后指定字段,这类似于我们编写 CDS 的方式。
SELECT SINGLE FROM @lt_flights AS flights FIELDS carrier_id, connection_id WHERE flight_date = @sy-datum AND city_from = 'FRANKFURT' INTO @DATA(todays_flight).
Temporary table using the clause WITH (7.51+)
使用关键字“WITH”,可以创建一个数据集,该数据集可以在查询中进一步使用。我们可以有多个这样的命名数据集,它们都需要以 + 开头。
WITH +cities AS ( SELECT cityfrom AS city FROM spfli WHERE carrid = 'LH' UNION DISTINCT SELECT cityto AS city FROM spfli WHERE carrid = 'LH' ) SELECT * FROM sgeocity WHERE city IN ( SELECT city FROM +cities ) INTO TABLE @DATA(result).
至此,我将结束这篇博文,并给您留下这样的想法——OPEN SQL 已经进化,现在我们也必须进化。不要在每次要构建报告时都使用 FOR ALL Entries。思考聚合、思考 WITH、思考 JOINS 以及所有可用的增强功能。