Oracle 实现小计、合计

最新推荐文章于 2023-09-04 16:58:26 发布

原创最新推荐文章于 2023-09-04 16:58:26 发布 · 8.2k 阅读

7 ·

CC 4.0 BY-SA版权

Oracle 专栏收录该内容

7 篇文章

订阅专栏

本文详细介绍使用groupbyrollup及union查询两种方法，在SQL中实现数据的小计和合计功能，适用于需要进行分组汇总的场景，提供实际的代码示例。

方法一：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;

效果图：