select * from(
select
j.ndate as ndate,
j.customer,
sum(j.amount) as sumAmount,
row_number() over(order by j.pid desc) rn
from
income.jk_debt j,
dic.jk_dic_money_type m,
dic.v_jk_dic_customer b,
dic.v_dic_unit s
where j.money_type=m.pid and j.customer=b.JK_DIC_CUSTOMER_FK and
j.unit=s.SUB_STATION_CODE and j.unit=b.UNIT
and j.ntype=1 and j.invalid is null and j.amount<0 and j.unit='HDP'
and j.ndate>=TO_DATE('2011-01-01','yyyy-MM-dd') and j.ndate<=TO_DATE('2011-11-14','yyyy-MM-dd')
group by rollup(j.pid,j.ndate,j.customer)
)where rn=1
内层的sum(j.amount)配合group by rollup()在分组计算,oracle中rollup中的字段与select后要查询的字段一一对应。常用到报表小计合计中。
内层select作为根据条件查询出来的数据集,row_number() over(order by j.pid desc) rn根据rowNumber标记。配合外层select rn=1查询
结果集中的最后一条记录集。