Oracle 实现小计、合计

炎同学要努力

于 2023-08-16 22:12:45 发布

阅读量1.3k

点赞数

CC 4.0 BY-SA版权

文章标签： oracle 数据库

本文链接：https://blog.csdn.net/weixin_43725328/article/details/132329537

本文介绍了在Oracle数据库中使用rollup函数进行小计和合计的SQL查询方法，以及如何通过union操作合并数据。作者还展示了如何使用decode和grouping函数实现数据分组和总计。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

首先：没有with rollup的方法。

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

方法二：利用union查询拼接

其他的方法后面再补充~~

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

select decode(grouping(to_char(rownum)),1,'合计',to_char(rownum)),a.clientaccnumber,a.clientaccount,sum(a.amount),a.contract_numberfrom (select clientaccnumber,clientaccount,amount,contract_number as contract_numberfrom lc_abc_export_data laedwhere deduct_info_id =  '3b6587b1f5ad4488879f99a810fb4097'and paynumber is not nullorder by cast(serialno as int)) agroup by rollup((to_char(rownum),a.clientaccnumber,a.clientaccount,a.contract_number))