时间戳

首页 > 解决方案 > 将查询分组为 3 列，并在 Apex Oracle 中显示另一列的总和

sql - 将查询分组为 3 列，并在 Apex Oracle 中显示另一列的总和

问题描述

我想做一个报告。在我的要求中，这并不难。我在此请求中指定日期和记录类型（正数或负数）。我希望此查询按字段 CONTRACTORID、ITEMID ORGANIZATION 分组。并在 NETTO 上提取金额。

select
  ID, DOCNUMBER,DOCDATE,PRINTNUMBER,DRIVERID,CONTRACTORID,ITEMID,
  BRUTTO,TARE,NETTO,
  DS_ID,
  DAT_A,
  MILEAGE,
  ORGANIZATION,
  sum(NETTO)
from WAYBILL
where trunc(DOCDATE) between to_date(:P115_D1,'dd.mm.yyyy')
                         and to_date(:P115_D2,'dd.mm.yyyy')
and DIRECTIONID = :P115_DIRECTIONID 
group by 
  ID, DOCNUMBER,DOCDATE,PRINTNUMBER,DRIVERID,CONTRACTORID,ITEMID,
  BRUTTO,TARE,NETTO,
  DS_ID,
  DAT_A,
  MILEAGE,
  ORGANIZATION

标签： sqloracleoracle-apex

解决方案

您似乎想要显示完整的行，每行都有其contractorid、itemid 和organization 的netto sum。您可以使用窗口函数来实现这一点：

select
  id,  docnumber, docdate, printnumber, driverid, contractorid, itemid, 
  brutto, tare, netto, ds_id, dat_a, mileage, organization,
  sum(netto) over (partition by contractorid, itemid, organization) as netto_sum
from waybill
where trunc(docdate) between to_date(:p115_d1,'dd.mm.yyyy')
                         and to_date(:p115_d2,'dd.mm.yyyy')
and directionid = :p115_directionid 
order by id;

推荐阅读