Oracle 实现小计、合计

方法一：group by rollup实现小计、合计

select decode(grouping(to_char(rownum)),1,'合计',to_char(rownum)),
       a.clientaccnumber,
       a.clientaccount,
       sum(a.amount),
       a.contract_number
  from (select clientaccnumber,
               clientaccount,
               amount,
               contract_number as contract_number
          from lc_abc_export_data laed
         where deduct_info_id =  '3b6587b1f5ad4488879f99a810fb4097'
           and paynumber is not null
         order by cast(serialno as int)) a
         group by rollup((to_char(rownum),a.clientaccnumber,a.clientaccount,a.contract_number))

方法二：利用union查询拼接

select to_char(rownum),
       a.clientaccnumber,
       a.clientaccount,
       a.amount,
       a.contract_number
  from (select clientaccnumber,
               clientaccount,
               amount,
               contract_number as contract_number
          from lc_abc_export_data laed
         where deduct_info_id =  '3b6587b1f5ad4488879f99a810fb4097'
           and paynumber is not null
         order by cast(serialno as int)) a
union
select '合计', '', '', sum(a.amount), ''
  from (select clientaccnumber,
               clientaccount,
               amount,
               contract_number as contract_number
          from lc_abc_export_data laed
         where deduct_info_id =  '3b6587b1f5ad4488879f99a810fb4097'
           and paynumber is not null
         order by cast(serialno as int)) a;

效果图：

参考 附：

https://blog.csdn.net/angeiqiang/article/details/50352804?tdsourcetag=s_pcqq_aiomsg

http://www.cnblogs.com/simplefrog/archive/2012/07/15/2592371.html