【IT168 技术文章】
5.having 详解:
HAVING cond:在group中选择出符合cond条件的记录。
DATA: count TYPE I,
avg TYPE F,
connid TYPE sbook-connid.
SELECT CONNID COUNT( * ) avg( LUGGWEIGHT )
INTO (connid, count, avg)
FROM sbook
WHERE
carrid = 'LH' AND
fldate = '20010228'
GROUP BY connid
HAVING AVG( luggweight ) > '20.0'.
WRITE: / connid, count, avg.
ENDSELECT.
HAVING (source_text):动态指定条件
PARAMETERS: comp(80) TYPE C,
tabname(80) TYPE C.
DATA: dref TYPE REF TO DATA,
long_name TYPE STRING,
count TYPE I,
fieldlist TYPE STRING,
condition TYPE STRING.
FIELD-SYMBOLS: <fs>.
CONCATENATE tabname '-' comp INTO long_name.
CREATE DATA dref TYPE (long_name).
ASSIGN dref->* TO <fs>.
CONCATENATE comp ' count(*)' INTO fieldlist.
condition = 'count(*) > 3'.
SELECT DISTINCT (fieldlist)
INTO (<fs>, count)
FROM (tabname)
GROUP BY (comp)
HAVING (condition).
WRITE: / <fs>, count.
ENDSELECT.
6. oder by 详解:
ORDER BY PRIMARY KEY:按照primary key升序排列记录。
DATA: wa_sbook TYPE sbook.
SELECT * FROM sbook INTO wa_sbook
WHERE
carrid = 'LH ' AND
connid = '0400' AND
fldate = '20010228'
ORDER BY PRIMARY KEY.
WRITE: / wa_sbook-bookid, wa_sbook-customid,
wa_sbook-custtype, wa_sbook-smoker,
wa_sbook-luggweight, wa_sbook-wunit,
wa_sbook-invoice.
ENDSELECT.
ORDER BY f1 ... fn:按照f1 … fn升序排列选择出来的数据。
DATA: wa_sflight TYPE sflight.
SELECT * FROM sflight INTO wa_sflight
WHERE carrid = 'LH' AND
fldate BETWEEN '20010227' AND '20010305'
ORDER BY planetype ASCENDING seatsocc DESCENDING.
WRITE: / wa_sflight-planetype, wa_sflight-seatsocc,
wa_sflight-connid, wa_sflight-fldate.
ENDSELECT.
ORDER BY (source_text):动态指定需要排序的字段。
PARAMETERS: comp(80).
DATA: dref TYPE REF TO DATA,
long_name TYPE STRING,
name TYPE STRING,
ftab TYPE TABLE OF STRING,
count TYPE I.
FIELD-SYMBOLS: <fs>.
name = 'SPFLI'.
CONCATENATE name '-' comp INTO long_name.
CREATE DATA dref TYPE (long_name).
ASSIGN dref->* TO <fs>.
APPEND comp TO ftab.
APPEND 'COUNT( * ) AS COUNT' TO ftab.
SELECT DISTINCT (ftab)
INTO (<fs>, count)
FROM (name)
WHERE
carrid = 'LH'
GROUP BY (comp)
ORDER BY (comp).
WRITE: / <fs>, count.
ENDSELECT.