数据库中 显示 小计 与 总计

select  rownum seq,

        decode(rn, 1, gid) gid,

        decode(rn, 1, gname) gname,

        gcolour,

        gwithin,

        gsize,

        gnumber

   from  ( select  t.*, row_number() over(partition  by  gid  order  by  gnumber) rn

           from  ( select  GId,

                        gname,

                        gcolour,

                        gwithin,

                        gsize,

                        sum (gnumber) gnumber

                   from  t_goods

                  group  by  GId, gname, gcolour, gwithin, gsize

                 union  all

                 select  gid, gname,  null ,  null ,  '小计' ,  sum (gnumber)

                   from  t_goods

                  group  by  gid, gname

                 union  all

                 select  null ,  null ,  null ,  null ,  '总计' ,  sum (gnumber)

                   from  t_goods) t);

 

  

        SEQ GID        GNAME                GCOLOUR       GWITHIN GSIZE         GNUMBER

---------- ---------- -------------------- ---------- ---------- ---------- --------

          1 A01        上衣                 红                  0 S                  10

          2                                 白                  0 M                  20

          3                                 黑                  0 L                  30

          4                                                       小计               60

          5 A02        下衣                 红                  0 S                   5

          6                                 白                  0 M                  15

          7                                                       小计               20

          8 A03        下衣                 红                  0 S                   5

          9                                 白                  0 M                  15

         10                                                       小计               20

         11                                                       总计              100

 

 

 

 

 

 

 

 

 

 

 

select t.*, row_number()OVER(PARTITION by ylmc order by gh ) rn
  from  (select gh , ylmc ,sum(syjc) ,sum(slyc) ,sum(smt) ,sum(syg) ,sum(gcls) ,sum(fmt) ,sum(fyp) ,sum(fyg) ,sum(gclf) ,sum(sysh) ,sum(fysh) ,sum(ccsh) ,sum(bygc) ,sum(byzc)
                   from zy_ylhz_two abc group by gh,ylmc
                 union ALL    
                    select '小计',ylmc,sum(syjc),sum(slyc),sum(smt),sum(syg),sum(gcls),sum(fmt),sum(fyp),sum(fyg),sum(gclf),sum(sysh),sum(fysh),sum(ccsh),sum(bygc),sum(byzc)
                    from zy_ylhz_two group by ylmc
                union ALL
                    select '总计','',sum(syjc),sum(slyc),sum(smt),sum(syg),sum(gcls),sum(fmt),sum(fyp),sum(fyg),sum(gclf),sum(sysh),sum(fysh),sum(ccsh),sum(bygc),sum(byzc)
                    from zy_ylhz_two )t

 

 

 

 

 

 

 

 

 

 

 

 

转载于:https://www.cnblogs.com/HeXiaoZhou/p/7903468.html