聚合查询中的数据并排序
在查询中,可以使用 totals 方法对列执行计算,并在生成的数据集中返回计算出的值。 这种计算通常称为聚合。
通常,您会结合使用聚合和分组来求出所指定列组的合计。 例如,您可以对“总帐条目”表中每个总帐科目的金额字段内的值进行求和,或在“物料分类帐条目”表中找到每个物料的平均数量。 Business Central 查询中的合计方法直接对应于 SQL Server 聚合函数。
您可以使用以下合计方法来完成特定任务:
Sum - 针对数据集中分组的所有记录,计算这些记录中所指定列的值的总和。
Average - 计算某个组中指定列内的平均值。
Min - 检索某个组中指定列内的最低值。
Max - 检索某个组中指定列内的最高值。
Count - 对于表示数据集中某个组的数据项表,返回这些表中的记录数。
随后的代码示例显示了如何在数量(基数)字段的 Method 属性中使用 Sum 方法。
query 7345 "Avail Qty. (Base) In QC Bins"
{
Caption = 'Avail Qty. (Base) In QC Bins';
elements
{
dataitem(Location; Location)
{
DataItemTableFilter = "Directed Put-away and Pick" = const(true);
dataitem(Warehouse_Entry; "Warehouse Entry")
{
DataItemLink = "Location Code" = Location.Code;
SqlJoinType = InnerJoin;
...
column(Sum_Qty_Base; "Qty. (Base)")
{
ColumnFilter = Sum_Qty_Base = filter(> 0);
Method = Sum;
}
...
}
}
}
}
您可以按查询中包含的任意列数,对由查询生成的数据集中的数据进行排序。 要定义排序方式,请在查询中设置 OrderBy 属性。 要对多个列进行排序,请使用逗号分隔每个列。
query 7345 "Avail Qty. (Base) In QC Bins"
{
Caption = 'Avail Qty. (Base) In QC Bins';
OrderBy = descending(Sum_Qty_Base);
...
}
无法按生成的数据集中不存在的列进行排序。 如果要按此类列进行排序,必须先将该列添加到查询模型中,然后将该列包括在 OrderBy 属性中。
从 Business Central 数据库中检索日期或 DateTime 字段时,您可能仅需要检索年、月或日,而非整个日期。 当您对数据进行分组和合计时,尤其适用这一理念。 例如,您可能需要按客户和年份对收入进行分组,或者按月份求出生产物料清单组成部分的平均消耗量。 您可以通过为查询中的 Date 或 DateTime 列选择一种日期方法来完成本任务。
日期方法包括:
Day
Month
Year
当使用 SQL Server 时,日期和时间值将使用协调世界时 (UTC) 进行处理。 如果 Business Central 解决方案使用 UTC 之外的时区,并且应用日期方法的字段的数据类型为 DateTime,则该字段的数据集中返回的日期值与表中相应字段对应的实际日期、月份或年份之间可能会有差异。 如果某个字段的对应 UTC 日期位于下一天或前一天,就会发生本情况,这是由 Business Central 的当天时间和时区所致。
从 Business Central 数据库表检索日期和时间值时,日、月或年会有差异。 这些值从 Business Central 解决方案的区域设置转换为 UTC 日期和时间。 日、月或年在 SQL Server 中进行计算,然后以整数形式返回到查询数据集。 本整数不考虑 Business Central 解决方案的区域设置。
为了避免本情况,您应该尽可能对具有 Date 数据类型(而非 DateTime 数据类型)的字段使用日期方法。 您还可以返回 DateTime 值并根据需要对日、月和年实施后处理。