【IT168 技术文章】
4.where 详解:
Where cond : 限定抽出数据的条件,如果没有where语句,则系统会抽出所有当前集团下的所有数据。
FOR ALL ENTRIES IN itab WHERE cond :把选择的条件限定在itab这个内表之中。当处理大量数据的时候,这条语句的性能相对来说比较好。
注意:只能够在select语句中使用。
Itab不需要表头行。
内表中作为条件的字段必须和数据库中的字段的类型相符。
条件中不能使用like,between,in语句。
不能使用oder by 语句。
SELECT * FROM sflight INTO wa_sflight
FOR ALL ENTRIES IN ftab
WHERE CARRID = ftab-carrid AND
CONNID = ftab-connid AND
fldate = '20010228'.
WRITE: / wa_sflight-carrid, wa_sflight-connid.
ENDSELECT.
WHERE (source_text):动态指定条件的时候使用。例如根据屏幕的选择来改变查询条件的情况下就可以使用。(source_text)可以是字符串,也可以是内表。
DATA: where_clause TYPE STRING,
connid TYPE sflight-connid.
CONCATENATE 'carrid = ''' airline ''''
' AND fldate = ''' date '''' INTO where_clause.
SELECT connid FROM sflight INTO connid
WHERE (where_clause).
WRITE: / date, airline, connid.
ENDSELECT.
5. group by 详解:
GROUP BY f1 ... fn:把f1 … fn 中指定的字段相同的数据归到一组。
DATA: count TYPE I, sum TYPE P DECIMALS 2, avg TYPE F.
DATA: connid TYPE sbook-connid.
SELECT connid COUNT( * ) SUM( luggweight ) AVG( luggweight )
INTO (connid, count, sum, avg)
FROM sbook
WHERE
carrid = 'LH' AND
fldate = '20010228'
GROUP BY connid.
WRITE: / connid, count, sum, avg.
ENDSELECT.
GROUP BY (source_text):动态指定以那个字段归组。(source_text)可以是字符串也可以是内表。
DATA: BEGIN OF wa.
INCLUDE STRUCTURE spfli.
DATA: count TYPE I.
DATA: END OF wa.
DATA: field_list TYPE STRING,
group_by_list TYPE STRING,
count TYPE I.
field_list = 'CITYFROM COUNT( * ) AS COUNT'.
group_by_list = 'CITYFROM'.
SELECT DISTINCT (field_list)
INTO CORRESPONDING FIELDS OF wa
FROM spfli
WHERE
carrid = 'LH'
GROUP BY (group_by_list).
WRITE: / wa-cityfrom, wa-count.
ENDSELECT.