SAP Open Sql Select 常规的用法

一、@tab, 在Select读取数据的同时，创建临时内表或者工作区

SELECT CARRID,CARRNAME
  FROM  scarr  
  INTO TABLE @DATA(result)

二、跨客户端取数 ，关键字‘CLIENT SPECIFIED’ 指定客户端

 SELECT CARRID,CARRNAME
   FROM  scarr CLIENT SPECIFIED
   INTO TABLE @DATA(result)
   WHERE MANDT = '300'
   .
 cl_demo_output=>display( result ).

三、FOR ALL ENTRIS?

DATA city TYPE spfli-cityfrom VALUE 'FRANKFURT'. 
cl_demo_input=>request( CHANGING field = city ). 

SELECT carrid, connid 
       FROM spfli 
       WHERE cityfrom = @( to_upper( city ) ) 
       INTO TABLE @DATA(entry_tab). 

IF entry_tab IS NOT INITIAL. 
  SELECT carrid, connid, fldate 
         FROM sflight 
         FOR ALL ENTRIES IN @entry_tab 
         WHERE carrid = @entry_tab-carrid AND 
               connid = @entry_tab-connid 
         ORDER BY PRIMARY KEY 
         INTO TABLE @DATA(result_tab). 
  cl_demo_output=>display( result_tab ). 
ENDIF. 

四、WITH(subquery_clauses)

WITH
  +flights AS ( SELECT FROM sflight
                       FIELDS carrid,
                              connid,
                              AVG( seatsocc AS DEC( 16,2 ) ) AS avg
                       WHERE carrid IN @carrids
                       GROUP BY carrid, connid
                       ORDER BY carrid, connid UP TO 1 ROWS )
                       ##db_feature_mode[limit_in_subselect_or_cte]
  SELECT FROM +flights AS f
           INNER JOIN scarr AS s
             ON f~carrid = s~carrid
         FIELDS s~carrname, f~connid, f~avg
         ORDER BY s~carrname, f~connid
         INTO TABLE @DATA(itab).
cl_demo_output=>display( itab ).

"The example shows a WITH statement, whose main query creates a tabular result set. As "the data is written into work area wa rather than to an internal table, a SELECT loop is "opened, which must be closed with ENDWITH. 
"当数据读进工作区而不是内表时，必须以ENDWITH结尾

WITH 
  +carriers AS ( SELECT FROM scarr 
                        FIELDS carrid, carrname ) 
  SELECT FROM spfli AS s 
           INNER JOIN +carriers AS c 
             ON s~carrid = c~carrid 
         FIELDS c~carrname, s~connid 
         WHERE s~carrid = 'UA' 
         INTO @DATA(wa) 
         UP TO 10 ROWS. 
  cl_demo_output=>write( wa ). 
ENDWITH. 
cl_demo_output=>display( ). 

五、关键字（DISTINCT）去重?

  TYPES:
       BEGIN OF struct,
         key1 TYPE c LENGTH 1,
         key2 TYPE c LENGTH 1,
         num1 TYPE i,
         num2 TYPE i,
       END OF struct.
   DATA: itab TYPE STANDARD TABLE OF struct ,
         istb type struct .

    istb = VALUE #( key1 = 1 key2 = 2 num1 = 3 num2 = 4 ).
    APPEND istb to itab .
    CLEAR istb .

    istb = VALUE #( key1 = 1 key2 = 2 num1 = 3 num2 = 4 ).
    APPEND istb to itab .
    CLEAR istb .

    istb = VALUE #( key1 = 2 key2 = 2 num1 = 3 num2 = 4 ).
    APPEND istb to itab .
    CLEAR istb .

    istb = VALUE #( key1 = 2 key2 = 2 num1 = 3 num2 = 4 ).
    APPEND istb to itab .
    CLEAR istb .

    istb = VALUE #( key1 = 3 key2 = 2 num1 = 3 num2 = 4 ).
    APPEND istb to itab .
    CLEAR istb .

    SELECT DISTINCT  key1,
                     key2,
                     num1,
                     num2
      FROM @itab AS itab
      INTO TABLE @data(result) .
    cl_demo_output=>display( result ).

?