在上一篇博客《SAP CDS View基础语法(创建你的第一个CDS View)》
中,我介绍了CDS View的基础语法。在本篇博客中,我将介绍一些CDS运算的进阶用法,包括聚集、JOIN和UNION三个部分。
1 聚集
用途:
完成聚集运算,例如MIN,MAX,COUNT,SUM等
语法:
@AbapCatalog.sqlViewName: 'ZDEMO_CDS_SQL'
@AbapCatalog.preserveKey: true
define view ZDEMO_CDS_DDL
as select from sflight
{
planetype,
min(price) as min_price,
max(price) as max_price,
sum(price) as sum_price,
count(*) as count_planes
}
group by
planetype
解释:
- 使用聚集运算时,要使用group by指定聚集的件,也即按哪些字段进行分组统计。
运行效果:
2 聚集运算中的Having语句
用途:
指定聚集运算过程中的筛选条件
语法:
@AbapCatalog.sqlViewName: 'ZDEMO_CDS_SQL'
@AbapCatalog.preserveKey: true
define view ZDEMO_CDS_DDL
as select from sflight
{
planetype,
min(price) as min_price,
max(price) as max_price,
sum(price) as sum_price,
count(*) as count_planes
}
group by
planetype
having
planetype = '747-400'
or count(*) > 60
解释:
- Having中指定的条件字段,只能是group by中的字段的子集;在Having中也可以使用聚集运算的中间结果集作为删选条件
运行效果:
3 JOIN
用途:
CDS支持Inner Join, Left Outer Join,Right Outer Join
语法:
@AbapCatalog.sqlViewName: 'ZDEMO_CDS_SQL'
@AbapCatalog.preserveKey: true
define view ZDEMO_CDS_DDL
as select from sbook as a
inner join sflight as b on a.carrid = b.carrid
and a.connid = b.connid
and a.fldate = b.fldate
{
a.carrid as airline_code,
a.connid as connection_number,
a.fldate as flight_date,
a.customid as customer_id,
b.planetype as planetype
}
where
a.carrid = 'DL'
解释:
- Inner Join, Left Outer Join, Right Outer Join的用法同ABAP OPEN SQL的用法一致
运行效果:
4 UNION
用途:
UNION两个SELECT的结果集
语法:
@AbapCatalog.sqlViewName: 'ZDEMO_CDS_SQL'
@AbapCatalog.preserveKey: true
define view ZDEMO_CDS_DDL
as select distinct from sbook
{
carrid as airline_code,
connid as connection_number,
fldate as flight_date
}
where
carrid = 'DL'
union
select distinct from sflight
{
carrid as airline_code,
connid as connection_number,
fldate as flight_date
}
where
planetype = '747-400'
解释:
- UNION可以合并两个SELECT的结果集,并自定去除重复的条目
- UNION ALL 合并结果集,保留重复的条目
- 合并的两个结果集要有相同字段数
- 合并结果集的列类型要兼容
- 字段名称要相同
运行效果:
5 小结
在本文中,我们进一步介绍了SAP CDS View的运算语法,相信通过这两篇博客,各位同学应该已经学会了创建CDS View,以及用CDS View完成运算逻辑。CDS View还有很多其他的用法,感兴趣的同学,可查看ABAP 的帮助文档。
高质量的SAP技术博客,欢迎关注❤️、点赞👍、转发📣!