ABAP核心进阶篇120篇SELECT查询语法优化12篇第四篇SELECT复杂查询实战——聚合函数、子查询与GROUP BY/HAVING组合用法博客标题《SELECT复杂查询实战聚合函数、子查询与GROUP BY/HAVING组合用法》博客简介针对复杂统计查询需求讲解COUNT/SUM/MAX等聚合函数的使用规则梳理子查询、嵌套查询、GROUP BY分组与HAVING条件过滤的组合逻辑结合订单金额统计、物料库存盘点等实际业务场景给出完整代码示例解决多维度数据统计的查询需求。 写在前面在实际业务场景中我们经常需要对数据进行多维度统计——按销售组织汇总订单金额、按工厂盘点库存、找出超过平均值的异常数据等。这些需求需要综合运用聚合函数、分组查询和子查询技术。复杂查询 聚合函数COUNT / SUM / AVG / MAX / MIN 分组过滤GROUP BY HAVING 子查询WHERE 子查询 / EXISTS统计行数、金额、平均值按维度分组过滤聚合结果作为筛选条件替代部分 JOIN通过本文的学习你将掌握COUNT/SUM/AVG/MAX/MIN的规范用法GROUP BY与HAVING的组合规则与常见误区WHERE 子查询与 EXISTS 子查询的实战区别订单金额统计、物料库存盘点等业务场景的完整代码一、聚合函数详解 1.1 常用聚合函数速查函数说明示例注意COUNT(*)统计总行数COUNT(*)包含 NULL 行COUNT(field)统计非空值数量COUNT(lifnr)忽略 NULLCOUNT(DISTINCT field)统计不重复值数量COUNT(DISTINCT auart)性能开销较大SUM(field)求和SUM(netwr)自动忽略 NULLAVG(field)平均值AVG(netwr)忽略 NULLMAX(field)/MIN(field)最大/最小值MAX(erdat)可用于日期和数值 1.2 基础统计示例 统计总订单数 SELECT COUNT(*) FROM ekko INTO DATA(lv_total). 统计有供应商的订单数忽略 lifnr 为空的行 SELECT COUNT(lifnr) FROM ekko INTO DATA(lv_with_vendor). 统计订单类型种类数 SELECT COUNT(DISTINCT auart) FROM ekko INTO DATA(lv_types). 销售订单总金额 SELECT SUM(netwr) FROM ekko INTO DATA(lv_amount) WHERE auart OR AND erdat 20230101. 最早和最晚订单日期 SELECT MIN(erdat), MAX(erdat) FROM ekko INTO (DATA(lv_min_date), DATA(lv_max_date)). 平均订单金额 SELECT AVG(netwr) FROM ekko INTO DATA(lv_avg).二、GROUP BY 分组与 HAVING 条件过滤 2.1 GROUP BY 核心规则重要SELECT 列表中出现的所有非聚合字段都必须包含在GROUP BY中否则语法错误。 正确非聚合字段 auart 在 GROUP BY 中 SELECT auart, COUNT(*) AS cnt, SUM(netwr) AS total FROM ekko GROUP BY auart INTO TABLE DATA(lt_stat). 多字段分组 SELECT auart, vkorg, COUNT(*) AS cnt, SUM(netwr) AS total FROM ekko GROUP BY auart, vkorg INTO TABLE DATA(lt_stat). 2.2 多聚合函数组合TYPES: BEGIN OF ty_stat, auart TYPE ekko-auart, order_count TYPE i, total_amount TYPE ekko-netwr, avg_amount TYPE p DECIMALS 2, max_amount TYPE ekko-netwr, min_amount TYPE ekko-netwr, END OF ty_stat. SELECT auart, COUNT(*) AS order_count, SUM(netwr) AS total_amount, AVG(netwr) AS avg_amount, MAX(netwr) AS max_amount, MIN(netwr) AS min_amount FROM ekko INTO CORRESPONDING FIELDS OF TABLE DATA(lt_stat) GROUP BY auart ORDER BY total_amount DESCENDING. 2.3 HAVING 条件过滤HAVING用于对分组后的聚合结果进行过滤必须与 GROUP BY 连用。 仅显示订单数超过 100 且总金额超过 100 万的类型 SELECT auart, COUNT(*) AS cnt, SUM(netwr) AS total FROM ekko WHERE erdat 20230101 分组前过滤 GROUP BY auart HAVING COUNT(*) 100 分组后过滤 AND SUM(netwr) 1000000 INTO TABLE DATA(lt_hot_types). 2.4 WHERE vs HAVING 对比WHEREHAVING执行时机分组前过滤行分组后过滤组能否使用聚合函数❌ 否✅ 是性能影响减少后续分组数据量过滤最终结果组合使用最佳实践能放在WHERE中的条件绝不放在HAVING中以减少 GROUP BY 的数据量。三、子查询详解 3.1 ABAP OPEN SQL 中子查询的限制在 ABAP OPEN SQL 中子查询主要用于WHERE 条件中常见形式WHERE field IN ( SELECT ... )WHERE EXISTS ( SELECT ... )WHERE field ( SELECT SINGLE ... )注意ABAP OPEN SQL不支持在 SELECT 列表中直接写子查询即不支持 Scalar Subquery也不支持在 FROM 子句中嵌套子查询。这些场景需要通过CDS 视图或多次查询来实现。 3.2 WHERE 子查询最常用 查询金额大于平均值的订单 SELECT * FROM ekko WHERE netwr ( SELECT AVG(netwr) FROM ekko ) INTO TABLE DATA(lt_above_avg) UP TO 100 ROWS. 查询属于销售订单类型的行项目IN 子查询 SELECT * FROM ekpo WHERE ebeln IN ( SELECT ebeln FROM ekko WHERE auart OR ) INTO TABLE DATA(lt_items) UP TO 100 ROWS. 查询不属于销售订单类型的行项目NOT IN 子查询 SELECT * FROM ekpo WHERE ebeln NOT IN ( SELECT ebeln FROM ekko WHERE auart OR ) INTO TABLE DATA(lt_items) UP TO 100 ROWS. 3.3 EXISTS 子查询性能优于 IN 查询至少有一个行项目的订单 SELECT * FROM ekko AS e WHERE EXISTS ( SELECT * FROM ekpo AS p WHERE p~ebeln e~ebeln ) INTO TABLE DATA(lt_with_items) UP TO 100 ROWS. 查询没有任何行项目的订单 SELECT * FROM ekko AS e WHERE NOT EXISTS ( SELECT * FROM ekpo AS p WHERE p~ebeln e~ebeln ) INTO TABLE DATA(lt_empty).性能提示EXISTS通常比IN更快特别是当子查询返回大量数据时。EXISTS找到第一条匹配记录即返回真而IN需要构建完整的子查询结果集。 3.4 子查询与 JOIN 的选择需求推荐方案原因需要显示关联表字段JOIN子查询无法获取外部表字段仅判断存在性EXISTS比 JOIN 更高效不产生重复行用聚合结果作条件WHERE子查询比先聚合再 JOIN 更直观四、业务场景一订单金额多维度统计 4.1 按销售组织订单类型统计TYPES: BEGIN OF ty_order_stat, vkorg TYPE ekko-vkorg, auart TYPE ekko-auart, order_count TYPE i, total_amount TYPE ekko-netwr, avg_amount TYPE p DECIMALS 2, max_amount TYPE ekko-netwr, min_amount TYPE ekko-netwr, END OF ty_order_stat. SELECT e~vkorg, e~auart, COUNT(*) AS order_count, SUM(e~netwr) AS total_amount, AVG(e~netwr) AS avg_amount, MAX(e~netwr) AS max_amount, MIN(e~netwr) AS min_amount FROM ekko AS e WHERE e~vkorg IN so_vkorg AND e~auart IN so_auart AND e~erdat IN so_erdat GROUP BY e~vkorg, e~auart HAVING SUM(e~netwr) 1000000 ORDER BY total_amount DESCENDING INTO TABLE DATA(lt_order_stat) UP TO 200 ROWS. 4.2 按月份统计订单金额SELECT SUBSTRING( erdat, 1, 4 ) AS year, SUBSTRING( erdat, 5, 2 ) AS month, COUNT(*) AS order_count, SUM(netwr) AS total_amount FROM ekko WHERE erdat 20230101 GROUP BY SUBSTRING( erdat, 1, 4 ), SUBSTRING( erdat, 5, 2 ) ORDER BY year, month INTO TABLE DATA(lt_monthly). 4.3 找出无行项目的异常订单SELECT ebeln, erdat, auart FROM ekko AS e WHERE NOT EXISTS ( SELECT * FROM ekpo WHERE ebeln e~ebeln ) AND erdat 20230101 INTO TABLE DATA(lt_empty_orders) UP TO 100 ROWS.五、业务场景二物料库存盘点 5.1 需求说明与数据模型盘点各工厂的物料库存找出库存异常库存为零或负数的物料。数据模型MARC物料工厂数据含标准价格STPRSMAKT物料描述MARD物料库存地点数据含非限制库存LABST注意MARD的粒度是“物料工厂库存地点”统计一个物料在某个工厂的总库存时需要用SUM(labst)聚合所有库存地点。 5.2 库存异常盘点已修正TYPES: BEGIN OF ty_inventory, werks TYPE marc-werks, matnr TYPE marc-matnr, maktx TYPE makt-maktx, total_stock TYPE i, total_value TYPE p DECIMALS 2, END OF ty_inventory. SELECT m~werks, m~matnr, t~maktx, SUM(s~labst) AS total_stock, SUM(s~labst) * COALESCE( m~stprs, 0 ) AS total_value FROM marc AS m LEFT JOIN makt AS t ON m~matnr t~matnr AND t~spras sy-langu LEFT JOIN mard AS s ON m~matnr s~matnr AND m~werks s~werks WHERE m~werks IN so_werks AND m~matnr IN so_matnr GROUP BY m~werks, m~matnr, t~maktx HAVING SUM(s~labst) 0 ORDER BY total_value DESCENDING INTO TABLE DATA(lt_abnormal).关键修正原代码中SUM(s~labst * COALESCE(m~stprs, 0))将乘法写在了聚合函数内部这在 ABAP OPEN SQL 中不被允许。改为SUM(s~labst) * COALESCE(m~stprs, 0)先汇总库存数量再乘以标准价格计算库存金额。六、常见错误与规避方案#错误写法问题正确写法1SELECT auart, vkorg, COUNT(*) ... GROUP BY auartvkorg 不在 GROUP BY 中将 vkorg 加入 GROUP BY2HAVING erdat 20230101HAVING 中使用普通字段条件将普通条件移到 WHERE3WHERE netwr (SELECT netwr FROM ekko WHERE auartOR)子查询可能返回多行改用IN或加SINGLE4SUM(netwr * waers)聚合函数内包含多字段运算改为SUM(netwr)后在外层计算5忘记处理 NULL 值聚合结果可能不准使用COALESCE(field, 0)6HAVING COUNT(*) 100前未写GROUP BY语法错误HAVING 必须与 GROUP BY 连用七、总结复杂查询聚合函数COUNT/SUM/AVG分组过滤GROUP BY HAVING子查询IN / EXISTS✅ 多维度数据统计知识点核心要点聚合函数COUNT/SUM/AVG/MAX/MIN支持DISTINCTGROUP BY所有非聚合字段必须包含在GROUP BY中HAVING仅用于聚合结果过滤必须与GROUP BY连用WHERE vs HAVINGWHERE 先过滤后分组HAVING 先分组后过滤子查询ABAP OPEN SQL 仅支持 WHERE 条件中的子查询SELECT 列表中不支持EXISTS vs INEXISTS性能通常更优大数据量时优先使用NULL 处理使用COALESCE处理 NULL避免聚合结果偏差核心要点聚合函数 GROUP BY 是多维度统计的基本公式HAVING 只能用于聚合函数条件普通字段条件应放在 WHERE 中ABAP OPEN SQL 中子查询主要用于 WHERE 条件SELECT 列表中不可用能用 JOIN 获取关联字段时不用子查询仅判断存在性时用 EXISTS聚合结果用于 ALV 时务必为金额/数量字段补全参考字段cfieldname/qfieldname聚合函数内避免写复杂表达式先聚合再计算下一篇预告《FOR ALL ENTRIES语法深度解析适用场景、边界限制与最佳实践》作者爱喝水的鱼丶版本记录2026年7月验证基准SAP NetWeaver 7.51 你在编写复杂统计查询时遇到过哪些语法限制欢迎留言交流