ABAP数据库操作系列（6）

５.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.