核心内容摘要
78岁的她，13年的坚持：一个关于时间、热爱与不朽的传奇
SELECT语句基础架构
1 完整的SELECT语法结构SELECT [SINGLE] [DISTINCT] [*|field1 field2 ...|aggregate(...)] FROM {dbtab [AS alias] | (source) | itab | join_expression} [CLIENT SPECIFIED] [BYPASSING BUFFER] [UP TO n ROWS] [OFFSET m] [INTO|APPENDING target] [WHERE condition] [GROUP BY fields [HAVING condition]] [ORDER BY fields] [FOR ALL ENTRIES IN itab] [FOR UPDATE] [PACKAGE SIZE p] [PREFETCH n] [%_HINTS db_hints].
数据源选择
1 标准数据库表 直接表名 SELECT * FROM sflight. 使用别名 SELECT * FROM sflight AS f. 跨客户端访问 SELECT * FROM sflight CLIENT SPECIFIED.
2 CDS视图 访问CDS视图 SELECT * FROM zi_sflight_sales WHERE carrid LH INTO TABLE DATA(lt_cds).
3 内表作为数据源ABAP
52DATA: lt_source TYPE TABLE OF sflight. 填充lt_source... 以内表作为查询源 SELECT * FROM lt_source AS source WHERE carrid LH INTO TABLE DATA(lt_result).
4 动态表名DATA: lv_tabname TYPE tabname VALUE SFLIGHT. SELECT * FROM (lv_tabname) INTO TABLE DATA(lt_dynamic).
字段选择详解
1 字段列表语法 选择所有字段 SELECT * FROM sflight. 选择特定字段 SELECT carrid connid fldate FROM sflight. 使用表达式 SELECT carrid, connid, price *
19 AS price_with_tax, CONCAT(carrid, connid) AS flight_no, CASE WHEN price 500 THEN HIGH ELSE NORMAL END AS price_level FROM sflight. 字段别名 SELECT carrid AS airline_code, connid AS connection_id FROM sflight.
2 系统字段 访问系统字段 SELECT carrid, mandt, sy-datum AS selection_date, sy-uzeit AS selection_time FROM sflight.
条件表达式WHERE
1 比较操作符 等于 WHERE carrid LH 不等于 WHERE carrid AA 大于小于 WHERE price 100 WHERE price 500 BETWEEN WHERE fldate BETWEEN 20230101 AND 20231231 LIKE通配符 WHERE name LIKE SMITH% 以SMITH开头 WHERE name LIKE %SMITH 以SMITH结尾 WHERE name LIKE %SMITH% 包含SMITH WHERE name LIKE SM_TH 单个字符匹配 WHERE name NOT LIKE %TEST% 不包含TEST IS [NOT] NULL WHERE cancelled IS NULL WHERE cancelled IS NOT NULL
2 IN操作符 值列表 WHERE carrid IN (LH, AA, UA) 范围表 DATA: lt_range TYPE RANGE OF s_carr_id. lt_range VALUE #( sign I option EQ ( low LH ) ( low AA ) ). WHERE carrid IN lt_range 子查询 WHERE carrid IN ( SELECT carrid FROM scarr WHERE currcode EUR )
3 复杂条件组合WHERE ( carrid LH AND connid 0400 ) OR ( carrid AA AND price 300 ) OR ( carrid NOT IN (UA, DL) AND fldate 20230101 )
4 动态WHERE条件DATA: lt_where TYPE TABLE OF string. DATA: lv_where TYPE string. APPEND CARRID LH TO lt_where. APPEND AND PRICE 100 TO lt_where. APPEND AND ( CANCELLED OR CANCELLED IS NULL ) TO lt_where. CONCATENATE LINES OF lt_where INTO lv_where SEPARATED BY space. SELECT * FROM sflight INTO TABLE DATA(lt_result) WHERE (lv_where).
JOIN操作深度解析
1 JOIN类型对比JOIN类型描述使用场景INNER JOIN只返回匹配的行需要确保两边都有数据LEFT OUTER JOIN返回左表所有行右表匹配不到的为NULL主表为主关联表可选RIGHT OUTER JOIN返回右表所有行左表匹配不到的为NULL关联表为主CROSS JOIN笛卡尔积很少使用
2 多表JOINSELECT f~carrid, f~connid, c~carrname, a~airpfrom, a~airpto, p~name FROM sflight AS f INNER JOIN scarr AS c ON f~carrid c~carrid INNER JOIN spfli AS p ON f~carrid p~carrid AND f~connid p~connid INNER JOIN sairport AS a ON p~airpfrom a~id WHERE f~carrid LH INTO TABLE DATA(lt_join).
3 自连接 查找同一航线的不同航班 SELECT a~carrid, a~connid AS flight1, b~connid AS flight2, a~airpfrom, a~airpto FROM spfli AS a INNER JOIN spfli AS b ON a~carrid b~carrid AND a~airpfrom b~airpfrom AND a~airpto b~airpto AND a~connid b~connid INTO TABLE DATA(lt_self_join).
4 使用USING子句简化 当连接字段名称相同时 SELECT * FROM sflight INNER JOIN scarr USING (carrid) INTO TABLE DATA(lt_using).
子查询全面用法
1 标量子查询返回单个值 在SELECT列表中 SELECT carrid, ( SELECT carrname FROM scarr WHERE carrid sflight~carrid ) AS carrname, price FROM sflight. 在WHERE条件中 SELECT * FROM sflight WHERE price ( SELECT AVG(price) FROM sflight ) 多列标量子查询 SELECT carrid, connid, ( SELECT carrname FROM scarr WHERE carrid sf~carrid ) AS carrname, ( SELECT cityfrom FROM spfli WHERE carrid sf~carrid AND connid sf~connid ) AS departure_city FROM sflight AS sf.
2 关联子查询 查找价格高于航线平均价格的航班 SELECT * FROM sflight AS sf1 WHERE price ( SELECT AVG(price) FROM sflight AS sf2 WHERE sf2~carrid sf1~carrid AND sf2~connid sf1~connid )
3 EXISTS/NOT EXISTS 存在订单的客户 SELECT * FROM scustom AS c WHERE EXISTS ( SELECT 1 FROM sbook WHERE customid c~id ) 没有订单的客户 SELECT * FROM scustom AS c WHERE NOT EXISTS ( SELECT 1 FROM sbook WHERE customid c~id )
4 IN子查询 使用IN SELECT * FROM sflight WHERE carrid IN ( SELECT carrid FROM scarr WHERE currcode EUR ) 使用NOT IN SELECT * FROM sflight WHERE carrid NOT IN ( SELECT carrid FROM scarr WHERE currcode USD )
聚合与分组
1 所有聚合函数SELECT carrid, COUNT(*) AS total_flights, COUNT( DISTINCT connid ) AS unique_routes, SUM( price ) AS total_revenue, AVG( price ) AS avg_price, MIN( price ) AS min_price, MAX( price ) AS max_price, STDDEV( price ) AS price_stddev, VARIANCE( price ) AS price_variance FROM sflight GROUP BY carrid INTO TABLE DATA(lt_aggregates).
2 多级分组SELECT carrid, connid, COUNT(*) AS flight_count, SUM( seatsocc ) AS total_passengers, AVG( seatsocc *
0 / seatsmax ) AS load_factor FROM sflight GROUP BY carrid, connid HAVING COUNT(*) 5 AND AVG( seatsocc *
0 / seatsmax )
7 INTO TABLE DATA(lt_group).
3 GROUPING SETSABAP
51 多个分组级别的聚合 SELECT carrid, connid, COUNT(*) AS count, SUM(price) AS total FROM sflight GROUP BY GROUPING SETS ( (carrid), (carrid, connid), () ) INTO TABLE DATA(lt_grouping_sets).
排序与窗口函数
1 排序选项 基本排序 SELECT * FROM sflight ORDER BY carrid ASCENDING, connid DESCENDING, fldate. 按表达式排序 SELECT * FROM sflight ORDER BY price *
19 DESCENDING. 动态排序 DATA: lv_order TYPE string VALUE CARRID DESCENDING. SELECT * FROM sflight ORDER BY (lv_order).
2 窗口函数ABAP
51 ROW_NUMBER SELECT carrid, connid, price, ROW_NUMBER( ) OVER( PARTITION BY carrid ORDER BY price DESC ) AS price_rank FROM sflight. RANK, DENSE_RANK SELECT carrid, connid, price, RANK( ) OVER( ORDER BY price DESC ) AS rank, DENSE_RANK( ) OVER( ORDER BY price DESC ) AS dense_rank FROM sflight. 累计聚合 SELECT carrid, connid, fldate, price, SUM( price ) OVER( PARTITION BY carrid ORDER BY fldate ) AS running_total, AVG( price ) OVER( PARTITION BY carrid ) AS avg_by_carrier FROM sflight. LAG/LEAD SELECT carrid, connid, fldate, price, LAG( price ) OVER( PARTITION BY carrid, connid ORDER BY fldate ) AS prev_price, LEAD( price ) OVER( PARTITION BY carrid, connid ORDER BY fldate ) AS next_price FROM sflight.
分页与限制
1 分页实现 传统分页 DATA: lv_offset TYPE i VALUE 0, lv_limit TYPE i VALUE 10, lv_total TYPE i. 获取总数 SELECT COUNT(*) FROM sflight INTO lv_total WHERE carrid LH. 分页查询 SELECT * FROM sflight INTO TABLE DATA(lt_page) WHERE carrid LH ORDER BY connid, fldate OFFSET lv_offset UP TO lv_limit ROWS.
2 TOP N查询 每个航空公司最贵的5个航班 SELECT * FROM sflight AS f1 WHERE ( SELECT COUNT(*) FROM sflight AS f2 WHERE f2~carrid f1~carrid AND f2~price f1~price ) 5 ORDER BY carrid, price DESC.
性能优化技术
1
1 索引使用策略 使用主键索引 SELECT * FROM sflight WHERE mandt sy-mandt 始终包含client AND carrid LH 主键字段1 AND connid 0400 主键字段2 AND fldate
主键字段3 使用二级索引 查看表SE11/SE11的索引选择合适的索引 SELECT * FROM sflight WHERE carrid LH AND fldate BETWEEN 20230101 AND
如果存在索引(CARRID, FLDATE)则效率高
1
2 FOR ALL ENTRIES优化 正确用法 IF lt_keys IS NOT INITIAL. SORT lt_keys BY carrid connid. DELETE ADJACENT DUPLICATES FROM lt_keys. SELECT * FROM sflight INTO TABLE lt_result FOR ALL ENTRIES IN lt_keys WHERE carrid lt_keys-carrid AND connid lt_keys-connid. ENDIF. 避免的问题
空内表 - 会查询所有数据
重复条目 - 性能下降
字段不匹配 - 类型、长度需一致
1
3 缓冲区策略 绕过SAP缓冲区实时数据 SELECT * FROM sflight BYPASSING BUFFER INTO TABLE DATA(lt_fresh). 单记录缓冲默认 TABLES参数中的表自动使用单记录缓冲 完全缓冲表 在SE11中设置缓冲选项
1
4 分块读取 PACKAGE SIZE分块处理大数据 SELECT * FROM sflight INTO TABLE DATA(lt_package) PACKAGE SIZE 1000 WHERE carrid LH. 处理数据块 PERFORM process_package USING lt_package. ENDSELECT. PREFETCH预读取 SELECT * FROM sflight INTO TABLE DATA(lt_data) PREFETCH 1000 UP TO 10000 ROWS.
高级特性
1
1 FOR UPDATE锁定 悲观锁定 SELECT * FROM sflight INTO DATA(ls_flight) WHERE carrid LH AND connid 0400 AND fldate 20230101 FOR UPDATE. 处理数据... UPDATE sflight SET price ls_flight-price *
1 WHERE carrid LH AND connid 0400 AND fldate
20230101.
1
2 动态SELECTDATA: lv_table TYPE tabname VALUE SFLIGHT, lt_fields TYPE TABLE OF string, lt_where TYPE TABLE OF string, lt_order TYPE TABLE OF string, lr_result TYPE REF TO data. FIELD-SYMBOLS: lt_table TYPE ANY TABLE. APPEND CARRID TO lt_fields. APPEND CONNID TO lt_fields. APPEND PRICE TO lt_fields. APPEND CARRID LH TO lt_where. APPEND AND PRICE 100 TO lt_where. APPEND CARRID ASCENDING TO lt_order. APPEND PRICE DESCENDING TO lt_order. 动态创建内表类型 CREATE DATA lr_result TYPE TABLE OF (lv_table). ASSIGN lr_result-* TO lt_table. 动态查询 SELECT (lt_fields) FROM (lv_table) INTO CORRESPONDING FIELDS OF TABLE lt_table WHERE (lt_where) ORDER BY (lt_order).
1
3 UNION/UNION ALL UNION去重 SELECT carrid, connid FROM sflight WHERE carrid LH UNION SELECT carrid, connid FROM sflight WHERE carrid AA INTO TABLE DATA(lt_union). UNION ALL不去重 SELECT carrid, connid FROM sflight WHERE price 500 UNION ALL SELECT carrid, connid FROM sflight WHERE seatsocc 100 INTO TABLE DATA(lt_union_all).
1
4 数据库提示 特定数据库优化提示 SELECT * FROM sflight %_HINTS DB6 INDEX(SFLIGHT SFLIGHT~PRIMARY) ORACLE INDEX(sflight sflight_pk) WHERE carrid LH.
错误处理与调试
1
1 异常处理TRY. SELECT * FROM sflight INTO TABLE DATA(lt_data) WHERE carrid lv_carrid. CATCH cx_sy_open_sql_db INTO DATA(lx_sql). 处理数据库错误 DATA(lv_error) lx_sql-get_text( ). MESSAGE lv_error TYPE E. CATCH cx_sy_dynamic_osql_error INTO DATA(lx_dyn). 处理动态SQL错误 MESSAGE lx_dyn-get_text( ) TYPE E. ENDTRY.
1
2 调试工具 SQL跟踪 ST05 - SQL Trace 执行计划 ST05 - Explain SQL 运行时分析 SE30 - Runtime Analysis 代码检查 SLIN - Code Inspector ATC - ABAP Test Cockpit
1
3 性能监控 记录查询时间 GET RUN TIME FIELD DATA(lv_start_time). SELECT * FROM sflight INTO TABLE DATA(lt_data). GET RUN TIME FIELD DATA(lv_end_time). DATA(lv_duration) lv_end_time - lv_start_time. WRITE: / 查询耗时:, lv_duration, 微秒. 记录数据库访问次数 在事务STAD中查看
最佳实践
总结
1
1 DOs✅ 使用新语法ABAP
4✅ 只选择需要的字段✅ 使用合适的索引✅ 批量操作优于循环✅ 使用FOR ALL ENTRIES时检查空表✅ 使用TRY…CATCH处理异常✅ 为大表查询设置UP TO限制✅ 定期分析SQL性能
1
2 DON’Ts❌ 避免SELECT *❌ 避免在循环中SELECT❌ 避免不必要的JOIN❌ 避免使用NOT IN使用NOT EXISTS❌ 避免在WHERE中对字段使用函数❌ 避免隐式类型转换❌ 不要忽略CLIENT处理
1
3 代码模板 标准SELECT模板 METHOD get_flight_data. DATA: lt_result TYPE TABLE OF ty_flight.
准备条件 DATA(lr_condition) prepare_condition( ).
执行查询 TRY. SELECT carrid, connid, fldate, price, seatsocc, seatsmax FROM sflight INTO CORRESPONDING FIELDS OF TABLE lt_result WHERE carrid IN lr_condition-carrid_range AND fldate BETWEEN lv_date_from AND lv_date_to ORDER BY carrid, connid, fldate UP TO 1000 ROWS. CATCH cx_sy_open_sql_db INTO DATA(lx_error). RAISE EXCEPTION TYPE zcx_db_error EXPORTING previous lx_error textid zcx_db_errorquery_failed. ENDTRY.
后处理 IF lt_result IS NOT INITIAL. process_result( CHANGING ct_data lt_result ). ENDIF.
返回结果 rt_result lt_result. ENDMETHOD.
版本特性差异版本新特性ABAP
40内联声明字符串模板ABAP
50窗口函数CDS视图增强ABAP
52内表作为数据源ABAP
54更多SQL表达式ABAP
56JSON处理增强S/4HANACDS视图为核心Open SQL增强
相关事务码和工具事务码用途SE11数据字典查看表结构SE16数据浏览器ST05SQL跟踪和性能分析SE30运行时分析SATABAP跟踪SLIN代码检查器DBACOCKPIT数据库监控这个完全指南涵盖了SAP ABAP SELECT语句的所有方面从基础到高级特性。
实际开发中应根据具体需求选择合适的查询方式并始终考虑性能和可维护性。