方法一: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