13. 将数据选择至内表(多行)
DATA: BEGIN OF tab_type,
carrid TYPE spfli-carrid,
connid TYPE spfli-connid,
END OF tab_type.
DATA: spfli_tab1 TYPE TABLE OF spfli,
spfli_tab2 LIKE TABLE OF tab_type.
SELECT *
FROM spfli
INTO TABLE spfli_tab1
WHERE cityfrom = 'NEW YORK' AND cityto = 'LONDON'.
SELECT carrid connid
FROM spfli
INTO CORRESPONDING FIELDS OF TABLE spfli_tab2
WHERE cityfrom = 'NEW YORK' AND cityto = 'FRANKFURT'.
LOOP AT spfli_tab2 INTO tab_type.
WRITE: / tab_type-carrid,
tab_type-connid.
ENDLOOP.
14. 动态指定查询条件
DATA: cond(72) TYPE c,
itab LIKE TABLE OF cond,
city1(10) VALUE 'FRANKFURT',
city2(10) VALUE 'NEW YORK',
itab_spfli LIKE TABLE OF spfli WITH HEADER LINE.
CONCATENATE 'CITYFROM = ''' city1 '''' INTO cond.
APPEND cond TO itab.
CONCATENATE 'OR CITYFROM = ''' city2 '''' INTO cond.
APPEND cond TO itab.
SELECT *
FROM spfli
INTO TABLE itab_spfli
WHERE (itab).
LOOP AT itab_spfli.
WRITE: /
itab_spfli-cityfrom,
itab_spfli-cityto.
ENDLOOP.
15. 多表结合查询-select语句嵌套
DATA: wa_carrid TYPE spfli-carrid,
wa_connid TYPE spfli-connid,
wa_carrname TYPE scarr-carrname.
SELECT carrid connid
FROM spfli
INTO (wa_carrid, wa_connid)
WHERE cityfrom = 'NEW YORK'.
SELECT carrname
FROM scarr
INTO wa_carrname
WHERE carrid = wa_carrid.
WRITE / wa_carrname.
ENDSELECT. "内循环结束
ENDSELECT. "外循环结束
每当在表SPFLI中查询到一个符合条件的carrid值,系统就重新对SCARR进行一次
查询
16. 多表结合查询-for all entries
DATA: BEGIN OF wa_spfli,
carrid TYPE spfli-carrid,
connid TYPE spfli-connid,
END OF wa_spfli,
BEGIN OF wa_scarr,
carrid TYPE scarr-carrid,
carrname TYPE scarr-carrname,
END OF wa_scarr,
spfli_tab LIKE TABLE OF wa_spfli.
SELECT carrid connid
FROM spfli
INTO TABLE spfli_tab
WHERE cityfrom = 'NEWYORK'.
SELECT carrid carrname
FROM scarr
INTO wa_scarr FOR ALL ENTRIES IN spfli_tab "比对spfli_tab中的每个元组
WHERE carrid = spfli_tab-carrid.
WRITE / wa_scarr-carrname.
ENDSELECT.
先将SPFLI中符合条件数据选进一个内表,再…
17. 组合查询
REPORT Z_SELECT_JOIN.
data: wa_carrid type spfli-carrid,
wa_connid type spfli-connid,
wa_carrname type scarr-carrname.
select spfli~carrid scarr~carrname spfli~connid
from spfli inner join scarr on scarr~carrid = spfli~carrid
into (wa_carrid, wa_carrname, wa_connid)
where spfli~cityfrom = 'NEW YORK'.
write: / wa_carrid, wa_carrname, wa_connid.
endselect.
18. 分组总计查询
data: carrid type sflight-carrid,
minimum type p decimals 2,
maximum type p decimals 2.
write: / 'Carrier ID',
at 15 'Minimum Price',
at 30 'Maximum Price'.
select carrid MIN( price ) MAX( price )
into (carrid, minimum, maximum)
from sflight
group by carrid
having MIN( price ) > 500
order by carrid descending.
write: / carrid, minimum, maximum.
endselect.
DATA: BEGIN OF tab_type,
carrid TYPE spfli-carrid,
connid TYPE spfli-connid,
END OF tab_type.
DATA: spfli_tab1 TYPE TABLE OF spfli,
spfli_tab2 LIKE TABLE OF tab_type.
SELECT *
FROM spfli
INTO TABLE spfli_tab1
WHERE cityfrom = 'NEW YORK' AND cityto = 'LONDON'.
SELECT carrid connid
FROM spfli
INTO CORRESPONDING FIELDS OF TABLE spfli_tab2
WHERE cityfrom = 'NEW YORK' AND cityto = 'FRANKFURT'.
LOOP AT spfli_tab2 INTO tab_type.
WRITE: / tab_type-carrid,
tab_type-connid.
ENDLOOP.
14. 动态指定查询条件
DATA: cond(72) TYPE c,
itab LIKE TABLE OF cond,
city1(10) VALUE 'FRANKFURT',
city2(10) VALUE 'NEW YORK',
itab_spfli LIKE TABLE OF spfli WITH HEADER LINE.
CONCATENATE 'CITYFROM = ''' city1 '''' INTO cond.
APPEND cond TO itab.
CONCATENATE 'OR CITYFROM = ''' city2 '''' INTO cond.
APPEND cond TO itab.
SELECT *
FROM spfli
INTO TABLE itab_spfli
WHERE (itab).
LOOP AT itab_spfli.
WRITE: /
itab_spfli-cityfrom,
itab_spfli-cityto.
ENDLOOP.
15. 多表结合查询-select语句嵌套
DATA: wa_carrid TYPE spfli-carrid,
wa_connid TYPE spfli-connid,
wa_carrname TYPE scarr-carrname.
SELECT carrid connid
FROM spfli
INTO (wa_carrid, wa_connid)
WHERE cityfrom = 'NEW YORK'.
SELECT carrname
FROM scarr
INTO wa_carrname
WHERE carrid = wa_carrid.
WRITE / wa_carrname.
ENDSELECT. "内循环结束
ENDSELECT. "外循环结束
每当在表SPFLI中查询到一个符合条件的carrid值,系统就重新对SCARR进行一次
查询
16. 多表结合查询-for all entries
DATA: BEGIN OF wa_spfli,
carrid TYPE spfli-carrid,
connid TYPE spfli-connid,
END OF wa_spfli,
BEGIN OF wa_scarr,
carrid TYPE scarr-carrid,
carrname TYPE scarr-carrname,
END OF wa_scarr,
spfli_tab LIKE TABLE OF wa_spfli.
SELECT carrid connid
FROM spfli
INTO TABLE spfli_tab
WHERE cityfrom = 'NEWYORK'.
SELECT carrid carrname
FROM scarr
INTO wa_scarr FOR ALL ENTRIES IN spfli_tab "比对spfli_tab中的每个元组
WHERE carrid = spfli_tab-carrid.
WRITE / wa_scarr-carrname.
ENDSELECT.
先将SPFLI中符合条件数据选进一个内表,再…
17. 组合查询
REPORT Z_SELECT_JOIN.
data: wa_carrid type spfli-carrid,
wa_connid type spfli-connid,
wa_carrname type scarr-carrname.
select spfli~carrid scarr~carrname spfli~connid
from spfli inner join scarr on scarr~carrid = spfli~carrid
into (wa_carrid, wa_carrname, wa_connid)
where spfli~cityfrom = 'NEW YORK'.
write: / wa_carrid, wa_carrname, wa_connid.
endselect.
18. 分组总计查询
data: carrid type sflight-carrid,
minimum type p decimals 2,
maximum type p decimals 2.
write: / 'Carrier ID',
at 15 'Minimum Price',
at 30 'Maximum Price'.
select carrid MIN( price ) MAX( price )
into (carrid, minimum, maximum)
from sflight
group by carrid
having MIN( price ) > 500
order by carrid descending.
write: / carrid, minimum, maximum.
endselect.